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.
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.
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 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.
- Fixed: Failed with error "Duplicate element name" (Thank you, Matt).
- Fixed: Failed to process input columns of type
DT_CY(Thank you, Gerald).
- Fixed: Modified to preserve the order of the columns from the input.
- New: A new parameter Format with a choice to create regular JSON files or files in JSON Lines format.
- New: A new parameter Skip NULLs to avoid including elements that are NULL.
- New: Introduced component.