|
SSIS JSON Generator Transform can be used to generate single or multiple JSON documents from any type of datasources (e.g. SQL Server, MySQL, Flat File, Excel) inside dataflow task.
|
Step-By-Step
In this tutorial we are going to show you How to Generate single JSON File from multiple source(parent child relationship).
You can use JSON files and SQL Tables source for JSON Generator Transform. In this tutorial we are going use SQL Tables Source.
This is same as XML Generator Transform.
Sample JSON (Root pattern).
{
"CustomerID" : "ALFKI",
"CustomerName" : "Alfreds Futterkiste",
"OrderList" : [
{
"OrderID" : 10248,
"OrderDate" : "1996-07-04 00:00:00"
}
],
"OrderAmount" : [
74143988.6909
]
}
-
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.
-
Double click on the Data Flow task to see Data Flow designer surface.
-
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.
-
In the above step you can use our Free ZS Dummy Data Source too. Click here for more information
-
We are going through SQL Server Database, need OLEDB Connection for Data Source.
How to create OLE DB Connection.
-
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.
-
Now, click on New Button for create Connection.
-
Let's Configure Connection Manager, just Follow steps one by one as we have created.
-
Click on OK button to save connection configure setting UI.
How to generate JSON file using JSOn Generator Transform.
-
Double click on OLEDB Source for configure it.
-
Lets Configure in Connection Manager, just follow below image steps.
-
Click on OK button to Save OLEDB Source Editor UI Settings.
-
Now, double click on ZS JSON Generator Transform to configure it.
-
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.
-
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.
-
Here, You can Add / Edit Dataset Name easily.

-
Lets Generate with Add/Edit Value Array.

-
Click on OK to save JSON Generator Transform UI settings.
-
Lets Double click on ZS Trash Destination to Configure it.
-
Thats all, Just Save and Run or Execute the Project.
How to export JSON in 2D Array format
If you have to export JSON as 2D array format then you can change following option.
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. |
OutputMode |
Determines JSON format and output mode (e.g. one JSON for all input rows or one JSON for each input row) |
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 |
Setting UI
Articles / Tutorials
Click here to see all articles for [SSIS JSON Generator Transform] category
|
Introduction In our previous blog we saw How to write data into CSV file in SSIS (GZip / Split). Now in this blog, we will see How to Pivot CSV Data in SSIS using CSV Source. It also supports Pivot mode so you can convert single CSV string value into Rows. In this article we […]
|
|
Introduction In our previous article we saw how to POST data to REST API using few different ways. Now let’s go one step further and discuss another common scenario to batch REST API requests in SSIS. For efficient data transfer many API provides you to submit multiple records in a single request. In this article […]
|
|
Introduction Google BigQuery is a fully managed Big Data platform to run queries against large scale data. In this article you will learn how to integrate Google BigQuery data into Microsoft SQL Server using SSIS. We will leverage highly flexible JSON based REST API Connector and OAuth Connection to import / export data from Google […]
|
|
Introduction Elasticsearch is a powerful engine that allows you to store, aggregate and, most importantly, search data in a very analytical way. In this tutorial, you will learn how to bulk load data from SQL Server to Elasticsearch with SSIS (part of SQL Server) and ZappySys PowerPack. The scope of this article will be to show how to import […]
|
|
Introduction In our previous article we discussed how to load data into MongoDB (Insert, Update or Upsert Mode) with various options. In this post we will more on how to create MongoDB Document for load process. We will see how to produce BSON (MongoDB Specific JSON which may have functions such as ObjectID, ISODate). This […]
|
|
Introduction In this article we will look at how to Read / Write Amazon DynamoDB in SSIS. ZappySys developed many AWS related components but in this article we will look at 3 Tasks/Components for DynamoDB Integration Scenarios (Read, Write, Update, Bulk Insert, Create / Drop Table etc.). We will discuss how to use SSIS DynamoDB Source Connector […]
|
|
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.