Using the JSON Merge Component

The JSON Merge component is a transformation component used to take incoming data from upstream SSIS source components and merge them into one SSIS column based on the JSON data structure defined in the component, which can be then consumed by a downstream pipeline component. 

Document Designer

The Document Designer page allows you to build the design of the document you are trying to write to, or import the design from an existing document.

JSON Merge Editor

The Document Designer includes the following four tabs:

  • Details View.
  • Diagram View.
  • Preview.
  • Additional Settings.

In the Details View tab, the top part of the page is used to manually configure the nodes in the design:

  • Add Node: This button will add a new node to your Document design.
  • Remove Nodes: This button will remove a node from your Document design.
  • Direction buttons: These buttons can be used to rearrange the position of the nodes.
  • Rename Nodes: This option allows you to specify how the node name should be represented.
    • Use Qualified Names: When this option is selected, the output/column name will be set to the full qualified node name based on the node location in the document.
    • Use Short Names: When this option is selected, the output/column name will be set to the given Node Name directly.
  • Filter Columns: This option allows you to show or hide certain Columns in the grid.
    • Show Basic Columns: When this option is selected, only basic columns will be shown in the grid.
    • Show All Columns: When this option is selected, all available columns will be shown in the grid.
  • Filter Nodes: This option allows you to filter the list of nodes shown in the grid by typing a keyword in the textbox.

The Details View grid consists of:

  • Node Type: This options allows specify the type of the Node in your document design, There are four options available:
    • Array.
    • Object
    • Value.
    • Raw: This type can be used when trying to write JSON data under a node exactly as it is in the document.
  • Node Name : The Name of the Node in the document.
  • Input/Column Name: The name which will be used for the node when writing to the document.
  • Is Repeated: This option allows you to specify if a node is repeated within a document. (Available when Show All Columns is selected)
  • Format String: This option allows you to specify the target output format for a string when converting datetime/number values to a string. It follows the same .Net formatting function found at this link. (Available when Show All Columns is selected)
  • Source type: The type of the input for a node such as a dedicated Input, Variable or a Column depending on the Node Type.
  • Node-Write Settings: This option allows you to specify the settings of each node such as the datatype or the Variable Name based on the selected Source Type.
  • JSON Merge Editor
    • Column Name: The name of the column.
    • Data Type: The data type of the field which can be changed accordingly.
    • Length: This option allows you to specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
    • Precision: This option allows you to specify the number of digits in a number.
    • Scale: This option allows you to specify the number of digits to the right of the decimal point in a number.
    • Code Page: This option allows you to specify the Code Page of the field.
    • Format String: This option allows you to specify the target output format for a string when converting datetime/number values to a string. It follows the same .Net formatting function found at this link.

In the Diagram View tab, you would be able to view the document design as a diagram where you can link different inputs with each other in order to create the hierarchy of your document and establish relationships between the different inputs:

JSON Merge Editor

Create New Input

This option allows you to create a new Input and link it to an existing input if required based on the intended document design.

Delete Selected Input

This option allows you to remove existing Inputs from the digram

Reset Layout

This option allows you to rest the layout of the digram which would reorganize the location of each input.

In the Preview tab, the component will show a sample of the document which will be created with test values.

In the Additional Settings tab, you would find the following options:

  • Encoding: The encoding of the document that will be written.
  • Null Mode: This option allows you to specify the handling of Null values.
  • Print Pretty: When true, the merged JSON will contain line breaks and indents. When false, the merged JSON will be a single line of text.
  • Merge Mode: Input rows can be batched and sent in a single request. There are 3 batch modes:
    • Every Row: This mode is like a batch size of 1. A single file will be created using the merged JSON from each input row.
    • Every Batch: This mode lets you specify the batch size. Some number of files will be created, each containing the merged JSON from the number of rows specified in the batch size property.
    • Entire Buffer: This mode is like an unlimited batch size. Only one file will be created, and it will contain the merged JSON from all the input rows.
  • Batch Size: When 'Every Batch' Merge mode is selected, the Batch Size can be specified.
  • 'Is Repeated' Text Qualifier:  This option allows you to specify the  Text Qualifier used in a document when the  Is Repeated property is set to True for one or more node. There are four options available:
    • Double-quote(“).
    • Single-quote (‘).
    • Tick (`).
    • None.
  • 'Is Repeated' Text Delimiter: This option allows you to specify the  Text Delimiter used in a document when the  Is Repeated property is set to True for one or more node. There are seven options available:
    • Newline (\n).
    • Carriage Return (\r).
    • Semicolon (;).
    • Colon (:).
    • Comma (,).
    • Tab (\t).
    • Vertical Bar (|).
Import

This option allows you to import the design of your document from one of the following five sources:

  • Designer Settings: Import the design from an existing .designer.settings file.
  • Input (Reset Design): Create the design based on the input columns for the upstream components.
  • JSON (Local File): Import the design based on a JSON file on your local file system.
  • JSON Schema (Local File): Import the design based on a JSON Schema file on your local file system.
Export

Designer Settings: This option allows you to export the current document design to a .designer.settings file which can be used later to import the same design in a different component.

Columns

The Columns Page of the JSON Merge Component allows you to map the columns from upstream components to the defined nodes for the designed document.

JSON Merge - Columns Page

  • Input Column: Select an Input Column from an upstream component.
  • JSON Field: This is the field you are writing data to
  • Data Type: This column indicates the type of value for the current field.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Output & Error Handling

This page allows you to specify how errors should be handled when they happen along with the name of the output column that will contain the merged JSON.

JSON Merge Editor - Error Handling Page

Merged JSON Column Name

The name of the output column that will contain the merged JSON.

Error Handling has three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the component.

JSON Merge Editor - Error Output