Using the JSON Merge Component

The JSON Merge Component is a transformation component used to merge input data into JSON. Define the relationship of inputs in the Design Page, configure column properties in the Columns Page, and refine formatting in the Advanced Page.

Design Page

Define the relationship of the Inputs in the Design Page.

JSON Merge Editor

To define relationships simply click and drag from a child field to a parent field. In the above example, there is a relationship from the Item.PurchaseOrderId field to PurchaseOrder.Id field. What this means when merging is PurchaseOrder nodes will contain Item nodes whose PurchaseOrderId field match the PurchaseOrder's Id field. If you need to remove a relationship on the design page, simply select the connecting arrow and press the 'Delete' key.

You cannot continue to the Columns Page until the relationship structure is valid. The most common cause for an invalid relationship is invalid root count. The root input is defined be an input who does not have a parent Input. There must be one and only one root input. To fix this, delete any unused inputs and adjust the relationships so that there is only one root.

Columns Page

Configure column/node specific merge settings.

JSON Merge Editor - Columns Page

When navigating to the Columns Page for the first time a default JSON structure will be generated. As specified in the note at the top of the Columns Page, you can add, remove, and move nodes. When adding nodes, a child node will be added to the currently selected node. If no node is selected then a root node is added. When you drag and drop one node onto another the moving node (and its children) will become a child of the target node. When you drag and drop one node onto no node (white space) then the moving node will become a root node. Note that some structural changes are not permitted as they would break the relationship defined on the Design Page. You can customize each node with the following properties:

JSON Name

This is the name of the node as it will appear in the JSON document.

Input Column

The column to use as the value for the node.

Static Value

The value of the node. This can be plain text or an SSIS variable. Note you will have to include quotes if you want the JSON value to be a string. Use this option instead of mapping a column if the value is the same for every occurrence of the node in the JSON document.

JSON Type

This defines how the node(s) will be printed in the JSON Document. There are 4 different JSON Types:

  • Array Of Objects   - a JSON array (square brackets [ ]) that contains multiple JSON objects (curly brackets { }).
  • Array Of Values - a JSON array (square brackets [ ]) that contains multiple JSON values (name-value pairs)
  • Object  -  a JSON object (curly brackets { }).
  • Value - a JSON value (name-value pair) that comes from an input column or a static value.

Advanced Page

Configure JSON and merge related settings here.

JSON Merge Editor - Advanced Page

Null Mode

This property specifies how to handle NULL values. There are three options:

  • Don't Show - This option will hide nodes whose values are null.
  • Empty String - This option will convert NULL values to an empty string.
  • Null Value - This option will write null.
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

Specify how to merge the inputs into JSON. There are three different Merge Modes:

  • Every Row - This creates a JSON document for each record in the root Input. Notice when this option is selected, the columns of the root input are directed to the Default Output.
  • Every Batch - This creates a JSON document for every batch. The size of the batch is determined by the Batch Size property, and a batch item is equivalent to one record in the root input. In other words, a batch size of 10 will create JSON documents that each contain 10 root input records.
  • Entire Buffer - This creates a single JSON document.
Batch Size

Specifies how many root input records should be in each JSON document. This option is only available if the Merge Mode is Every Batch.

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