SSIS JSON File Destination
PreviousNext

SSIS JSON File Destination can be used to generate nested JSON file from single or multiple input datasources (e.g. SQL Server, MySQL, Flat File, Excel) inside dataflow task.

Download SSIS PowerPack

Content

Video Tutorial


Below video is showing JSON Generator Transform UI rather than JSON File Destination but concept / UI to generate nested JSON is almost identical in both components. In JSON Generator you have Output column for JSON and with JSON File destination you have to choose File Connection as destination.

Step-By-Step

In this tutorial we will see how to extract data into JSON File Format from multiple source(parent-child relationship). You can use SQL Tables source for generate JSON file. In this tutorial we are going use ZS DummyData Source. This is same as JSON Generator Transform.

Sample JSON (Root pattern).
{
    "CustomerID" : "BERGS",
    "CustomerName" : "Berglunds snabbk?p",
    "OrderList" : [
        {
            "OrderID" : 10278,
            "OrderDate" : "1996-08-12 00:00:00"
        },
        {
            "OrderID" : 10280,
            "OrderDate" : "1996-08-14 00:00:00"
        }
    ],
    "OrderAmount" : [
        4610488.9066,
        61010761.2468
    ]
}
  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 DummyData Source and JSON File Destination in the design surface and join the tasks with the blue arrow.
    SSIS JSON File Destination and DummyData Source - Drag and Drop
  6. Here, you can use our OLE DB Data Source too. Click here for more information.
  7. But, we are going through DummyData Source and File Connection for CSV File Destination.

How to create File Connection for JSON File Destination.

  1. Let's, Right click on Connection Managers Panel to Create File Connection, so you can use Destination Path, and Context Menu will appear, Select New Connection from the Context Menu. In the File Connection Manager, select the Usage type and specify the path of the JSON file.
    SSIS New File Connection
  2. Click on OK button to save connection configure setting UI.

How to Generate JSON File using JSON File Destination.

  1. Double click on DummyData Source to configure it, select Customer and Orders profile from Template dropdown.
  2. Once you change Template you will notice that lot of columns automatically added.
  3. Double click on JSON File Destination to configure it, Select File Connection we have already created. Check on Overwrite target file.

    Set Output to Mode Output Single JSON - Multi Content Format and Check on Indent Output (pretty print).
    SSIS JSON File Destination - Configure
  4. Now, Right Click on Mappings from Left Panel of JSON File Destination, Select Add Element(s) (Below this node). And than, In Add/Edit Attribute dialogbox for Select Add Multiple (Bound) and Click on Columns for Data.
    SSIS JSON File Destination - Configure
  5. Click on OK to save attribute Settings.
  6. Lets Generate Array, In Add/Edit Element (Array of rows) follow below Image steps.
    SSIS JSON File Destination - Array
  7. Click on OK to save Add Element Array Settings.
  8. Here, we can Add/Edit Value of Array.
    JSON File Destination - Add/Edit Value Array
  9. Click on OK to save Settings.
  10. Thats all, Just Save and Run or Execute the Project.
    SSIS JSON File Destination and DummyData Source - Execute

How to Edit Dataset.

  1. Double click on JSON File destination to configure it.
  2. Select Dataset and Click on Edit button, You can change setting in Add/Edit Dataset dialogbox.
    JSON File Destination - Add/Edit input Dataset
  3. Click on OK to save Settings.

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.
OutputMode Determines JSON format and output mode (e.g. one JSON for all input rows or one JSON for each input row)

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

Option Description
SingleFileMultiContentFormat [0] Output Single JSON - Multi Content Format (Batching Allowed)
SingleFileArrayFormat [1] Output Single JSON - Array Format (Batching Mode Allowed)
MultiFileFormat [2] Output One JSON per Parent Input Row
SingleDatasetArrayFormat [3] Single Dataset Array (Batching Allowed)
LayoutInfo Defines shape of output JSON string
DoNotOutputNullAttribute Do not output attribute if value is NULL. By default null attribute is output as "MyColname" : null
DoNotOutputEmptyNestedAttribute Set this option to True if you dont want to output empty nodes of sub document (e.g nested property). This option is only applicable if you enabled DoNotOutputNullProperty. If all child peoprties are NULL for parent node then by default output contains empty nodes (e.g. CountryInfo: { CityInfo: { } } for Json --OR-- <CountryInfo><CityInfo /></CountryInfo> for Xml). You can enable this option to avoid such empty nodes from the output.
DoNotOutputEmptyArrayAttribute Set this option to True if you dont want to output empty array nodes when no record found for that array (e.g. "Orders": [ ] for JSON --OR-- <Orders></Orders> for Xml). You can enable this option to avoid such empty nodes from the output.
IndentOutput Indent JSON Text generated by this transform (Pretty Print)
DateTimeFormat Default output format for datetime columns
Culture Culture setting for this task. This option is useful to control output format for certain data types (e.g. number, decimal) based on regional settings. In USA dot is used for decimal indicator but in Germany it may be comma. Leave this blank if you want to use system default settings.
UseColumnForHeaderFooter Document Header/Footer text come from upstream column rather than direct string
DocumentHeader Header text for document (Added before each document in the output)
DocumentFooter Footer text for document (Added after each document in the output)
ArrayBatchSize Specifies how many rows to include in each batch (Array records for single dataset array mode). 0=Include all in one batch. This setting is only valid for Mode=[Single Dataset Array]
OverwriteIfExists Overwrite target file if already exists
Append Append to file if exists else create new file. When this option is true OverwriteIfExists is always true.
Encoding Specifies encoding for data. If you set CharacterSet property then Encoing is ignored.

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

Option Description
Default [0] Default
ASCII [1] ASCII
UTF8 [2] UTF-8
UTF16 [3] UTF-16 LE (i.e. Unicode Little Endian)
UTF32 [4] UTF-32
UTF8WithoutBOM [5] UTF-8 Without BOM
UTF32WithoutBOM [6] UTF-32 Without BOM
UTF7 [7] UTF-7
UTF7WithoutBOM [8] UTF-7 Without BOM
UTF16WithoutBOM [9] UTF-16 Without BOM
BigEndian [10] UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM [11] UTF-16 BE Without BOM

Setting UI

JSON File Destination - Setting UI
JSON File Destination - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS JSON File Destination] category
Calling SharePoint 365 REST API in SSIS

Calling SharePoint 365 REST API in SSIS

Introduction to REST API with SharePoint in SSIS Using REST API with SharePoint in SSIS is a common requirement to Administer SharePoint. SharePoint 365 is a nice Microsoft application in the Cloud used to share documents and collaborate with the company. You can have schedules, projects, documents and more shared using a Web platform. In addition, […]


5 Ways to Export JSON from SQL Server using SSIS

5 Ways to Export JSON from SQL Server using SSIS

Introduction SQL Server 2012 and all previous versions lacking native JSON support, same theory is true for their ETL Platform SSIS. If you are one of them who wants to export JSON from SQL Server or any other RDBMS like MySQL, Oracle then you can easily output complex JSON using any of the following components. Five ways of […]



Copyrights reserved. ZappySys LLC.