SSIS JSON Generator Transform
Build single or multiple JSON documents from any data source inside the Data Flow, as a JSON string column.
JSON Generator Transform builds single or multiple JSON documents from any type of data source (e.g. SQL Server, MySQL, Flat File, Excel) inside the Data Flow. Join several inputs into parent/child nested JSON with a drag-and-drop layout and live preview, then pass the JSON string column downstream, for example to POST batches of rows to a REST API or load documents into MongoDB.
The trial includes JSON Generator Transform and every other PowerPack component
- Data Flow Transform in SSIS
- Runs in your environment
- Also runs in Azure Data Factory
- Fully functional 30-day trial
At a glance
What JSON Generator Transform can do
JSON Generator Transform creates single or multiple JSON documents from any type of data source (e.g. SQL Server, MySQL, Flat File, Excel) inside the Data Flow Task, and outputs them as a JSON string column.
JSON inside the Data Flow
Generate single or multiple JSON documents from any data source inside the Data Flow Task.
- Outputs a JSON string column to feed downstream systems
- Feed the JSON column to Web API Destination, MongoDB Destination or another downstream component
Nested JSON from multiple datasets
Join different types of datasets (e.g. SQL Server, Flat File, Excel) to create parent-child relations for nested JSON.
Drag & drop layout with preview
Build simple or nested JSON with a drag-and-drop layout and preview the structure as you change it.
- Sub document, Value Array and Document Array
One, many or batched documents
Create one JSON document for all input records, one document per ROOT dataset row, or one document per batch of rows to POST to REST API services.
- Choose the mode with the
OutputModeproperty - Batching example: one JSON document for every 10 input rows
Compact 2D arrays
Output a Document Array or Value Array as a compact 2D array.
- Output as Compact 2D Array checkbox in Add/Edit Element
- Array2D, Array2DWithHeader, ArrayLines and ArrayLinesWithHeader
Raw JSON values
Treat the value of a column as raw JSON, for example to build MongoDB BSON documents.
NULL handling & logging
Skip NULL attributes in the output and control logging.
DoNotOutputNullAttributeLoggingMode: set to Debugging for maximum log
SQL Server 2012–2025 & ADF
Runs in your environment on SQL Server 2012–2025 and Azure-SSIS.
- SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit)
- Azure Data Factory (SSIS IR)
- Design packages in Visual Studio 2017, 2019, 2022, or 2026
- Windows only (64-bit OS): Windows 7–11 or Windows Server 2012–2025
See all features
- Create simple or nested JSON documents inside the Data Flow Task with drag and drop
- Create nested JSON from multiple datasets
- Join different types of datasets (e.g. SQL Server, Flat File, Excel) for parent-child relations
- Single JSON document for all input records or one document per ROOT dataset row
- Data batching to POST to REST API services
- Preview JSON structure as you change the layout
- Sub document, Value Array and Document Array support
- Compact 2D array output
- Treat column values as raw JSON (e.g. BSON fragments for MongoDB)
- JSON string output column for downstream components
- Skip NULL attributes in the output
Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014, 2012 (32/64-bit) and Azure Data Factory
SQL Server 2012–2025
How it works
From the toolbox to your first run.
Shape the JSON, choose how rows become documents, and pass the result downstream.
Shape nested JSON with drag and drop.
Connect one or more upstream sources, then double-click JSON Generator Transform. Right-click Mappings and choose Add Element(s) with Add Multiple (Bound) to pick columns, and add value arrays or document arrays for child rows. The preview updates as you change the layout.
- Sub documents, value arrays and document arrays
- Parent/child relations across different input types
- Skip NULL attributes with
DoNotOutputNullAttribute
Group rows into one JSON document per batch.
Many APIs accept multiple records in a single request. Use the batch option to produce one JSON document for a set number of input rows, for example every 10 rows, and send each document with Web API Destination.
- One document for all rows, per ROOT row, or per batch
- Optional BatchID column at the root level
- Fewer API calls for bulk operations
Build MongoDB documents with raw JSON values.
Check Treat value of this column as Raw JSON so BSON fragments such as ObjectID or ISODate built in your SQL query are written as-is, then load the documents with MongoDB Destination.
- Raw JSON values inserted without escaping
- Works with MongoDB insert, update or upsert loads
- JSON string column feeds any downstream component
Output an array as a compact 2D array.
Add a Document Array or Value Array, then check Output as Compact 2D Array in the Add/Edit Element dialog. For rows with columns id and name, Array2D gives [[1,"AA"],[2,"BB"]].
- Array2D and Array2DWithHeader
- ArrayLines and ArrayLinesWithHeader
- Default array of documents or multi-content output
Inside the UI
Key settings, as they appear in SSDT.
Real screens from the JSON Generator Transform product page. Step through them, or open any shot at full size.
Add/Edit dataset
Add or rename the datasets (inputs) that make up the JSON document; one is the ROOT dataset.
Articles
Worked examples that use JSON Generator Transform.
Compatibility and requirements
Designed to stay inside your SSIS runtime.
- SQL Server 2012 through 2025, 32-bit and 64-bit
- Windows only, 64-bit OS required. SQL Server Express and Web editions do not include SSIS
- Azure Data Factory SSIS Integration Runtime
- Self-hosted: credentials and data stay in your environment (external API calls still reach that service)
- Fully functional 30-day trial of SSIS PowerPack
FAQ
Questions teams ask before they drop JSON Generator Transform on the canvas.
What is JSON Generator Transform?
A Data Flow transform in SSIS PowerPack that builds single or multiple JSON documents from any type of data source (e.g. SQL Server, MySQL, Flat File, Excel) and outputs them as a JSON string column for downstream components. A drag-and-drop layout editor with live preview handles nested and parent/child JSON.
How do I send batches of rows to a REST API?
Use the batch option to produce one JSON document per group of input rows (for example every 10 rows), then POST each document with Web API Destination. See how to batch REST API requests in SSIS.
Can I build MongoDB documents with ObjectID or ISODate?
Yes. Build the BSON fragments in your source query and check Treat value of this column as Raw JSON so they are written as-is. See how to create MongoDB documents in SSIS.
How is it different from JSON File Destination?
The layout editor is almost identical. JSON Generator Transform outputs JSON in a column for further processing in the Data Flow, while JSON File Destination writes the JSON straight to a file.
Which SQL Server versions can I run it on?
SQL Server 2025, 2022, 2019, 2017, 2016, 2014, and 2012 (32- and 64-bit), plus Azure Data Factory with an SSIS Integration Runtime (packages must target SQL Server 2017). See Compatibility and requirements.
What customers say
Start your free trial today
Install SSIS PowerPack and drag JSON Generator Transform onto the Data Flow.
Shape nested JSON from any Data Flow source and pass it to APIs, MongoDB or any downstream component.
- Data Flow Transform in SSISJSON Generator Transform appears in the toolbox after you install PowerPack.
- Fully functional 30-day trialSSIS PowerPack, running in your environment.
- Live supportHelp during your trial and after purchase.
Open Help FileBrowse all SSIS PowerPack tasks
New purchases may be eligible for refund review within 30 days. See the refund policy.
Talk to an engineer before or during your trial.