Content

• Video Tutorial
• Step-By-Step
• Properties
• Setting UI
• See Also

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.
   
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.
   
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.
   
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).
   
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.
   
5. Click on OK to save attribute Settings.
6. Lets Generate Array, In Add/Edit Element (Array of rows) follow below Image steps.
   
7. Click on OK to save Add Element Array Settings.
8. Here, we can Add/Edit Value of Array.
   
9. Click on OK to save Settings.
10. Thats all, Just Save and Run or Execute the Project.
    

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.
   
3. Click on OK to save Settings.

How to export JSON in 2D Array format

If you have to export JSON as 2D array format then you can change following option.

You can select either Add Document Array or Add Value Array option based on your requirement, just like the below screenshot.

In Add/Edit Element modal, you can select Output as Compact 2D Array checkbox, as highlighted in the below screenshot.

Here are examples of output for each option. Select say you have source data with 2 columns id and name.

Default

[{id:1,name:"AA"}, {id:2,name:"BB"}]

Multicontent

{id:1,name:"AA"}{id:2,name:"BB"}

ArrayDocs

[{id:1,name:"AA"}, {id:2,name:"BB"}]

Array2D

[[1,"AA"],[2,"BB"]]

Array2DWithHeader

[["id","name"],[1,"AA"],[2,"BB"]]

ArrayLines

[1,"AA"][2,"BB"]

ArrayLinesWithHeader

["id","name"][1,"AA"][2,"BB"]

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

See Also

• SSIS JSON Generator Transform
• SSIS XML Generator Transform
• SSIS XML File Destination
• SSIS JSON Source Connector
• SSIS Export JSON File Task
• SSIS JSON Parser Task
• SSIS MongoDB Source Adapter
• SSIS Azure Table Storage Source Adapter
• SSIS Amazon S3 Storage Task
• SSIS Amazon Storage Connection Manager

Articles / Tutorials

Click here to see all articles for [SSIS JSON File Destination] category

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