SSIS Excel Destination
PreviousNext

SSIS Excel File Destination Connector (Advanced Excel Destination) can be used to write data to Excel file from any source (e.g. SQL Server, Oracle, MySQL). When you use this component you don�t need any additional Microsoft Office Driver to write to excel.

Download SSIS PowerPack

Content

Video Tutorial

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).
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. 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
  6. Here, you can use OLE DB Data Source, and CSV File Source.
  7. But, we are going through JSON Source (REST API or Files) and we need Excel Connection for Excel Destination.

How to Create Excel Connection.

  1. 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
  2. Select ZS-EXCEL Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS Excel Source - Connection
  3. Now select the excel file path and click Test Connection in opened excel connection manager window.
    SSIS Excel Source - Connection Configure
  4. Click on OK button to save connection configure setting UI.

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

  1. Double click on JSON Source (REST API or File) for configure it.
  2. 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
  3. Click OK to save settings.
  4. Lets, double click on Excel Destination to configure it.
  5. Now, In the Connection Managers, select the Excel Connection Manager we have created before.
    SSIS Excel File Destination - Connection Manager
  6. 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
  7. In the Input Columns Tab, check the columns that you want to load data.
    SSIS Excel File Destination - Input Columns
  8. Click on OK button to save Excel Destination configure setting UI.
  9. 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.

Setting UI

SSIS Excel File Destination - Setting UI
SSIS Excel File Destination - Setting UI
SSIS Excel File Destination - Setting UI
SSIS Excel File Destination - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Excel Destination] category
SSIS PowerPack v2.9.5 released (Maintenance Release)

SSIS PowerPack v2.9.5 released (Maintenance Release)

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 […]


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

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

Introduction In this post, we will learn how to Create Excel File in SSIS from source like JSON / XML.  We will use SSIS PowerPack to connect and query a JSON or XML file. This article also covers creating Excel from JSON File. JSON stands for JavaScript Object Notation and it is an Open and Standard format to […]



Copyrights reserved. ZappySys LLC.