Content

• Video Tutorial
• Step-By-Step
• Properties
• Settings UI
• See Also
• References

Video Tutorial

Step-By-Step

{
    "CustomerID" : "ALFKI",
    "CustomerName" : "Alfreds Futterkiste",
    "OrderList" : [
        {
            "OrderID" : 10248,
            "OrderDate" : "1996-07-04 00:00:00"
        }
    ],
    "OrderAmount" : [
        74143988.6909
    ]
}

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 OLEDB Source, ZS JSON Generator Transform and ZS Trash Destination in the design panel and join the components with the blue arrow.
   
6. In the above step you can use our Free ZS Dummy Data Source too. Click here for more information
7. We are going through SQL Server Database, need OLEDB Connection for Data Source.

How to create OLE DB Connection.

1. Let's, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
   
2. Now, click on New Button for create Connection.
   
3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
   
4. Click on OK button to save connection configure setting UI.

How to generate JSON file using JSOn Generator Transform.

1. Double click on OLEDB Source for configure it.
2. Lets Configure in Connection Manager, just follow below image steps.
   
3. Click on OK button to Save OLEDB Source Editor UI Settings.
4. Now, double click on ZS JSON Generator Transform to configure it.
5. Now, Right Click on Mappings from left panel of JSON Generator Transform, select Add Element(s)(Below this node). And than, In Add/Edit Attribute dialogbox, select Add Multiple (Bound) and select column.
   
6. Now, Right Click on Mappings from left panel of JSON Generator Transform, select Add Value Array (Below this node). And than, configure Add/Edit Element (Array of rows) like below image.
   
7. Here, You can Add / Edit Dataset Name easily.
   
   
8. Lets Generate with Add/Edit Value Array.
   
   
9. Click on OK to save JSON Generator Transform UI settings.
10. Lets Double click on ZS Trash Destination to Configure it.
    
    
11. Thats all, Just Save and Run or Execute the Project.
    

How to generate JSON in 2D Array format

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"}]

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

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

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

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

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

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

Properties

Setting UI

See Also

• SSIS JSON File Destination
• 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