JSON Generator Transform

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.

Features

  • Create simple or nested JSON documents inside DataFlow Task using simple drag and drop approach
  • Ability to create nested JSON from Multiple datasets
  • Support Data Batching to POST to REST API services
  • Ability to create single JSON document for all input records or create one document for each input row from ROOT dataset
  • Preview JSON structure as you change layout on User Interface
  • Support Sub document, Value Array and Document Array
  • Ability to join different type of datasets (e.g. SQL Server, Flat File, Excel) to create parent-child relation for nested JSON
  • Support for SQL Server 2022, 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory
  • Articles/Tutorials: JSON related articles
Download Help File Buy
View All Tasks
ScreenshotsArticles / Useful LinksSystem Requirements

SSIS JSON Generator Transform – Add/Edit Dataset

JSON Generator Transform - Add/Edit Dataset

JSON Generator Transform – Add/Edit Dataset

SSIS JSON Generator Transform – Add/Edit Attribute

JSON Generator Transform - Add/Edit Attribute

JSON Generator Transform – Add/Edit Attribute

SSIS JSON Generator Transform – Add/Edit Document Array

JSON Generator Transform - Add/Edit Document Array

JSON Generator Transform – Add/Edit Document Array

SSIS JSON Generator Transform – Add/Edit Value Array

JSON Generator Transform - Add/Edit Value Array

JSON Generator Transform – Add/Edit Value Array

SSIS JSON Generator Transform – Layout Editor (Define nested elements and Parent-Child relation)

JSON Generator Transform - Layout Editor (Define nested elements and Parent-Child relation)

JSON Generator Transform – Layout Editor (Define nested elements and Parent-Child relation)

SSIS JSON Generator Transform – Test JSON Path Settings

JSON Generator Transform - Test JSON Path Settings

JSON Generator Transform – Generate JSON documents and load into MongoDB

Batch records in multiple JSON documents

Batch records in multiple JSON documents

Batch records in multiple JSON documents

How to generate JSON in 2D Array format

If you have to generate 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.

JSON Generator OR Destination - Add Document OR Value Array

JSON Generator OR Destination – Add Document OR Value Array

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

JSON Generator OR Destination - Add Edit Element for 2D Array

JSON Generator OR Destination – Add Edit Element for 2D Array

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

Articles / Tutorials

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


How to Pivot CSV Data in SSIS

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)

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

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

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

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

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

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

See Also

Articles/Posts

Oracle Logo

Oracle CSV Export from Table or Query Data

Contents1 Introduction2 Download and Install ZappyShell Command Line Tools3 Download and Install Oracle Drivers3.1 Sample Oracle ODBC Connection String3.2 Sample Oracle OLEDB Connection String4 Oracle CSV Export – Single table5 Oracle CSV Export – Multiple tables6 Oracle CSV Export – Multiple tables (using pattern)7 Oracle CSV Export – SQL Query8 Oracle CSV Export – Split […]

SQl Server Logo

SQL Server Excel Export from Table or Query data

Contents1 Introduction2 The problem3 Solution3.1 Export Tables to CSV Using – ZappyShell Command Line Tools3.2 Export Tables to CSV Using – SSIS Export CSV File Task4 Download and Install ZappyShell Command Line Tools5 SQL Server Excel Export – Single table6 SQL Server Excel Export – Multiple tables7 SQL Server Excel Export – Multiple tables (using […]

SQl Server Logo

SQL Server JSON Export from Table or Query Data

Contents1 Introduction2 The problem3 Solution4 Export SQL Server data to JSON using SSIS PowerPack5 Export SQL Server data to JSON using ZappyShell Command line6 SQL Server JSON Export – Single table7 SQL Server JSON Export – Multiple tables8 SQL Server JSON Export – Multiple tables (using pattern)9 SQL Server JSON Export – SQL Query10 Using […]

SQl Server Logo

SQL Server CSV Export from Table or Query Data

Contents1 Introduction2 Download and Install ZappyShell Command Line Tools3 Download and Install SQL Server Drivers3.1 Sample ADO.net Connection String (No driver needed)3.2 Sample ODBC Connection String (SQL Native Client Driver)3.3 Sample OLEDB Connection String (SQL Native Client Driver)4 SQL Server CSV Export – Single table5 SQL Server CSV Export – Multiple tables6 SQL Server CSV […]

MySQL CSV Export from Table or Query result

Contents1 Introduction2 Download and Install MySQL ODBC Driver3 Download and Install ZappyShell Command Line Tools4 MySQL CSV Export – Single table5 MySQL CSV Export – Multiple tables6 MySQL CSV Export – Multiple tables (using pattern)7 MySQL CSV Export – SQL Query8 MySQL CSV Export – Split Files8.1 MySQL CSV Export – Split By Row Count8.2 […]

MySQL Excel Export from Table or Query result

Contents1 Introduction2 The problem3 Solution4 Download and Install MySQL ODBC Driver5 Download and Install ZappyShell Command Line Tools6 MySQL Excel Export – Single table7 MySQL Excel Export – Multiple tables8 MySQL Excel Export – Multiple tables (using pattern)9 MySQL Excel Export – SQL Query10 MySQL Excel Export – Split Files and Tabs10.1 MySQL Excel Export […]

MySQL JSON Export from Table or Query result

Contents1 Introduction2 The problem3 Solution4 Download and Install MySQL ODBC Driver5 Download and Install ZappyShell Command Line Tools6 MySQL JSON Export – Single table7 MySQL JSON Export – Multiple tables8 MySQL JSON Export – Multiple tables (using pattern)9 MySQL JSON Export – SQL Query10 Using Map File for nested MySQL JSON Export11 MySQL JSON Export […]

References

Click here to learn more about System Requirements

Download View All Tasks Like This