SSIS Excel Destination

Step-By-Step

In this tutorial we will learn how to load data into Excel from Source using ZS Excel Destination (In this case its from JSON Source).

Firstly, You need to Download and Install SSIS ZappySys PowerPack.

Once you finished first step, Open Visual Studio and Create New SSIS Package Project.

Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
SSIS Data Flow Task - Drag and Drop

Double click on the Data Flow task to see Data Flow designer surface.

Here, In Visual Studio, drag and drop the ZS JSON Source (REST API or File) and Excel File Destination in the design surface and join the components with the blue arrow.
SSIS JSON Source and Excel File Destination - Drag and Drop

SSIS JSON Source and Excel File Destination - Drag and Drop

Here, you can use OLE DB Data Source, and CSV File Source.

But, we are going through JSON Source (REST API or Files) and we need Excel Connection for Excel Destination.

How to Create Excel Connection.

Right click on Connection Managers Panel to Create New ZS-Excel Connection and Context Menu will appear, Select New Connection from the Context Menu.
SSIS Create New Connection

Select ZS-EXCEL Connection Manager from the Connection Managers list and Click on Add Button.
SSIS Excel Source - Connection

Now select the excel file path and click Test Connection in opened excel connection manager window.
SSIS Excel Source - Connection Configure

Click on OK button to save connection configure setting UI.

How to Extract Data from JSON to Excel File using Excel Destination.

Double click on JSON Source (REST API or File) for configure it.

From the Access Mode dropdown select [File path or web Url], select JSON format file. You can download files from following URL for this example.

https://zappysys.com/downloads/files/test/invoices.json
https://zappysys.com/downloads/files/test/large-cust-1.json
https://zappysys.com/downloads/files/test/arrayfile-1.json

Now enter JSONPath expression in JSONPath textbox to extract only specific part of JSON file as below ($.value[*] will get content of value attribute from JSON document. Value attribute is array of JSON documents so we have to use [*] to indicate we want all records of that array).

$.value[*]

Unchecked on Output column for FileName and Include Parent (slow).
SSIS JSON Source File - Configure

Click OK to save settings.

Lets, double click on Excel Destination to configure it.

Now, In the Connection Managers, select the Excel Connection Manager we have created before.
SSIS Excel File Destination - Connection Manager

In the Component Properties, set the Append property to False, set the Overwrite to True. Write A1 in Range Start Cell and set TableName to $last_sheet$ (Write in the Last worksheet of Excel).
SSIS Excel File Destination - Component Properties

SSIS Excel File Destination - Component Properties

In the Input Columns Tab, check the columns that you want to load data.
SSIS Excel File Destination - Input Columns

Click on OK button to save Excel Destination configure setting UI.

Finally, run the package and the data will be exported from source to destination immediately.
SSIS Excel File Destination - Run or Execute

Properties

Property Name

Description

LoggingMode

LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option	Description
Normal [0]	Normal
Medium [1]	Medium
Detailed [2]	Detailed
Debugging [3]	Debugging

PrefixTimestamp

When you enable this property it will prefix timestamp before Log messages.

TableName

Sheet name or named range indicator from which you like to read data. You can use special table name indicators like [$first_sheet$] or [$1$], [$last_sheet$] or [$SomeNumber$]. You can also use Regular expression to get data from multiple sheets with matching name. Syntax:[regex-pattern]--regex (e.g. MySheet_\d+--regex), for example to get all rows from Sheet names Sales-01,Sales-02..Sales-12 you can set Table name as Sheet-(\\d+)--regex . For named range you can use NamedRange:NAME_FOR_RANGE

Headerless

Data has no header row to indicate column names

TreatValueAsFormula

Set this option to true if your value may be formula. When this option is true then any Value starting with = sign will be treated as Formula (e.g. =A1+C1)

RangeStartCell

Range start address for data read/write. Address must be excel cell address (e.g. A1)

Append

Start appending records if existing data found on the target sheet

RangeToClearBeforeWrite

Range you want to clear before writing data (Example: A1:M25). This is helpful when you writing data on existing Sheet and you want to make sure no previous content left in specified area before writing.

Overwrite

Overwrite existing file

ClearTargetSheet

When this option is true it clears all used cells in the target sheet before writing any data.

PreserveFormatting

Preserve target cell format when clear target sheet option is turned on.

What’s New In this release we focused on fixing some bugs with Dynamics CRM Source , Dynamics CRM Destination and Reporting Services Task. Version 2.9.6.10621 [Jun 24, 2019] New Features/Improvements NEW: Excel Source – Add option to read from any sheet by number rather than hard coded table name (e.g. $4$ , $5$ … ) Bug fixes […]

Content

Video Tutorial

Step-By-Step

How to Create Excel Connection.

How to Extract Data from JSON to Excel File using Excel Destination.

Properties

Setting UI

See Also

Articles / Tutorials

SSIS PowerPack v2.9.5 released (Maintenance Release)

Create Excel File in SSIS (Read from JSON / XML)