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
$JSONcolumn). - 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.
- In the SSIS Toolbox, locate the COZYROC's JSON Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, provide JSON data into JSON text editor. Also keep in mind if you did provide multiple objects array, only the first object data will be used to configure the component's metadata.
- 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
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.