
COZYROC JSON components, part of COZYROC SSIS+ suite, are third-party plug-ins for Microsoft SSIS that make it easy to parse and generate JSON files. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The JSON integration package consists of:
- JSON Source component
- Reads and parses JSON from a variable, file or direct text.
- JSON Transformation script
- Extract structured data from a text column that stores arrays with objects in JSON. An optional transformation function is used to convert a raw array to JSON array.
- JSON Destination component
- Generates a JSON data.
Demonstration
JSON Source

In this section we will show you how to set up a JSON Source component.
- When clicking on Columns tab the component would prepare the outputs and external columns by analyzing the existing data in the JSON text editor. Please note that the JSON Source can have multiple outputs (see the article about composite records), which columns you can see. The data in these outputs can be processed by downstream transformation and destination components(e.g. multiple OLE DB Destinations can store the data in SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the JSON Source component.
JSON Destination

In this section we will show you how to set up a JSON Destination component.
- Double-click on the component on the canvas.
- Once the component editor opens, select the destination where generated JSON data will be stored: File or Variable, then provide JSON sample into JSON text editor. Also keep in mind if you did provide multiple objects array into JSON text editor, only the first object will be used to configure the component's metadata.
- When clicking on Mapping tab the component would prepare the inputs and external columns by analyzing the existing sample JSON data in the JSON text editor. Please note that the JSON Destination can have multiple inputs (see the article about composite records), which columns you can see. The data in these inputs can be processed by upstream transformation and source components (e.g. a Query Transformation can be used to retrieve the necessary data from SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the JSON Destination component.
Overview
JSON Source Component is SSIS Data Flow Component for retrieving data from JSON document that supports multiple outputs via the composite records pattern.
- Supports the following JSON sources: String (i.e. direct value), Variable and File. The source JSON document needs to be an array of JSON elements.
- Component metadata for Variable and File sources is initialized from a provided sample JSON element from the source JSON array.
- Supports composite outputs. Besides the root JSON Source Output that contains the top-level fields, for any nested JSON arrays, corresponding composite outputs get populated.
- Supports a standalone output for retrieving the JSON elements of the array as a raw JSON string (a single
$JSON
column). - Supports an error output for redirecting problematic records (in case of a failure processing the field values a JSON document).
Quick Start
In this section we will show you how to set up a JSON Source component.
- When clicking on Columns tab the component would prepare the outputs and external columns by analyzing the existing data in the JSON text editor. Please note that the JSON Source can have multiple outputs (see the article about composite records), which columns you can see. The data in these outputs can be processed by downstream transformation and destination components(e.g. multiple OLE DB Destinations can store the data in SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the JSON Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Indicate the source of JSON data. The following options are available:
Value Description String The JSON data is provided as a direct value in the JSON editor. Variable The JSON data is available in a variable. Selecting this value displays the dynamic parameter JsonStringVariable. File The JSON data is available in a file. Selecting this value displays the dynamic parameter JsonFile and JsonFileEncodingCodePage. Available when Variable source selected.
Select or create variable, which will store JSON data.Available when File source selected.
Select the file containing JSON data.Available when File source selected.
Select the encoding code page for the file containing JSON data. Default is 65001 (Unicode UTF-8).Depending on selected source.
Source Description String The actual JSON string that will be populated. Variable or File The JSON sample which will be used to create the outputs, columns names and column types. Specify input file format. This property has the options listed in the following table.
Value Description JSON Input file in JSON format. JSON Lines Input file in JSON Lines format.
Knowledge Base
- Where can I find the documentation for the JSON Source?
- JSON Source: How to modify the Json File's name dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- New: Support for stream input.
- New: Improved processing of type-specified columns.
- New: A new parameter Format with a choice to process regular JSON files or files in JSON Lines format.
- New: Introduced component.
Related documentation
Overview
Data flow transformation to extract structured data from a text column, storing arrays with objects in JSON, XML or other text format. An optional transformation function is used to convert raw array to JSON array.
Setup
The script has the following parameters:
- DataColumn - specifies columns containing the input data. When multiple columns are selected, the content parameter passed to the Transform function will be a JavaScript object where each selected input column will be configured as an object member.
- Sample - sample data to initialize metadata. It would be used for the creation of output columns. If the sample is changed the output columns will be deleted and recreated.
- Transform - specifies transformation function to create JSON array before output. underscore.js functions are also available for use by default. The parameter of this function is the whole input array. To quickly test the transformation function, one of the online JavaScript Editors - JS Bin can be used. Here is the example transformation where "Price" is multiplied by the exchange rate. When a single input column is selected the contents are passed without modification. The function should parse the input. When multiple columns are selected the script tries to parse the data, if not possible the data is passed unchanged. All columns data is passed as a JavaScript object where property names are the column names and values containing the column data.
- IsSequence - specifies if the result is a single record or multiple. When a single record is selected the output is synchronous, otherwise asynchronous.
- IncludeSequenceTerminator - specifies if an empty row will be added to the output after the processing of an input row. This is useful when input rows contain arrays of multiple objects, e.g. in the context of SSIS+ support for composite records.
Handling different types of input format
The default transformation function expects JSON array as a string. If your input is different, you can customize the transformation function according to your needs. Please check the QuickStart(s) below for further information.
Quick Start
In this quick-start we will setup a JSON Transformation.
Providing sample data
The format of the sample data is array of JSON objects. All objects are processed and from their properties the output columns are created. The columns are created immediately after changing value of the parameter. If "Sample" is changed the current output columns are deleted and the new are created.
In this quick-start we will setup a XML Transformation.
Providing sample data
The format of the sample data is XML text. The XML is converted to JSON and only the array is returned. This is done by the transformation function. The sample data is in XML format because the input data is also in XML format. After the JSON array is returned from transformation all objects are processed and from their properties the output columns are created. The columns are created immediately after changing value of the parameter. If "Sample" is changed the current output columns are deleted and the new are created.
Sample data used:
XML elements with name person
will be converted to JavaScript Array.
Configuration
To use this script, you would need to load it in COZYROC JavaScript Component. If you are using COZYROC SSIS+ 2.0 or later, after selecting the corresponding script type and opening the component editor, you can select the script from a dropdown list with the pre-built scripts. For COZYROC SSIS+ 1.9, you can download the JavaScript file and browse to it via the "Import JavaScript code" button.

Knowledge Base
Overview
JSON Destination Component is SSIS Data Flow Component for generating JSON documents that supports multiple inputs via the composite records pattern.
- The generated JSON document is a JSON array. For each row of the main input a JSON element will be created. If there are additional inputs in the hierarchy of inputs they will be used for populating nested JSON arrays (see the composite records pattern).
- The generated JSON document can be written to a file or stored in a variable.
- Component metadata is initialized from a provided sample JSON element.
Quick Start
In this section we will show you how to set up a JSON Destination component.
- Double-click on the component on the canvas.
- Once the component editor opens, select the destination where generated JSON data will be stored: File or Variable, then provide JSON sample into JSON text editor. Also keep in mind if you did provide multiple objects array into JSON text editor, only the first object will be used to configure the component's metadata.
- When clicking on Mapping tab the component would prepare the inputs and external columns by analyzing the existing sample JSON data in the JSON text editor. Please note that the JSON Destination can have multiple inputs (see the article about composite records), which columns you can see. The data in these inputs can be processed by upstream transformation and source components (e.g. a Query Transformation can be used to retrieve the necessary data from SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the JSON Destination component.
Parameters
Configuration
Use the parameters below to configure the component.
Indicates where JSON document will be stored. The following options are available:
Value Description File Generated JSON document will be stored into file. Selecting this value will create FILE connection manager. Variable Generated JSON document will be stored into selected variable. Selecting this value displays the dynamic parameter DestinationVariable. Available when File destination type selected.
Select an existing File Connection Manager or create new.Available when Variable destination type selected.
Select or create variable, which will store generated by the component JSON document.The JSON sample which will be used to create the inputs, columns names and column types.
Specify output file format. This property has the options listed in the following table.
Value Description JSON Create output file in JSON format. JSON Lines Create output file in JSON Lines format. Specify how fields containing NULL value should be handled. This property has the options listed in the following table.
Value Description True The fields containing NULL values are not included. False The fields containing NULL values are included.
What's New
Related documentation
JSON Source
- New: Support for stream input.
- New: Improved processing of type-specified columns.
- New: A new parameter Format with a choice to process regular JSON files or files in JSON Lines format.
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the JSON Source?
- Where can I find the documentation for the JSON Destination?
- JSON Source: How to modify the Json File's name dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.