SSIS JSON Generator Transform

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.
SSIS Data Flow Task - Drag and Drop

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.
SSIS JSON Generator - Drag and Drop

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.
SSIS OLEDB - Connection

Now, click on New Button for create Connection.
SSIS OLEDB - Connection

Let's Configure Connection Manager, just Follow steps one by one as we have created.
SSIS OLEDB - Connection

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.
SSIS OLEDB Connection Configure

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.
SSIS JSON Generator - Drag and Drop

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.
SSIS JSON Generator - Drag and Drop

Here, You can Add / Edit Dataset Name easily.
JSON Generator Transform - Add/Edit input Dataset

Lets Generate with Add/Edit Value Array.
JSON Generator Transform - Add/Edit Value Array

Click on OK to save JSON Generator Transform UI settings.

Lets Double click on ZS Trash Destination to Configure it.
SSIS Trash Destination - Drag and Drop

Thats all, Just Save and Run or Execute the Project.
SSIS JSON Generator Execute

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

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

Articles / Tutorials

Click here to see all articles for [SSIS JSON Generator Transform] category

How to Pivot CSV Data in SSIS

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

How to batch REST API requests in SSIS (Bulk Operation)

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

How to read / write data in Google BigQuery using SSIS

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

Loading data from SQL Server to Elasticsearch with SSIS

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

How to create MongoDB documents (JSON, BSON) for loading in SSIS

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

How to Read / Write Amazon DynamoDB in SSIS

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

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

Content

Video Tutorial