Using the JSON Destination Component

The JSON Destination component does something similar to the JSON Merge component, but it is a destination component, which means that you can specify the component to write the merged JSON document to a local file or you can send it to a HTTP URL to perform a RESTful service call.

HTTP Request Page

The HTTP request page determines how merged JSON documents are used. The connection manager property can either be set to an HTTP Connection Manager which is used to send the document as part of an HTTP request, or a local file which is used to output the merged JSON to a file.

Local File

SSIS JSON Destination - Local File

  • Local File Path - The path on the file system where the JSON document will be saved.

HTTP Connection Manager

SSIS JSON Destination - HTTP

If an HTTP connection manager is selected the UI will display request settings that are used to determine the content of HTTP requests.

HTTP Method
The HTTP method that will be used to send requests.
Relative Path
The relative path is the second part of the URL that will be used to make the HTTP request. It is combined with the base path from the connection manager to form the full path. The relative path can contain query string parameters, but they can also be added individually in the grid below.
Query String Parameters
Query string parameters that will be appended to the full path when sending HTTP requests.
Request Headers
Request headers that will be combined with the headers in the connection manager and included with HTTP requests.
Request Body
The body of the HTTP request.
Batch 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 web request will be sent or file created using the merged JSON from each input row.
  • Entire Buffer - This mode is like an unlimited batch size.  Only one web request or file will be created, and it will contain the merged JSON from all the input rows.
  • Every Batch - This mode lets you specify the batch size.  Some number of web requests will be sent or files created, each containing the merged JSON from the number of rows specified in the batch size property.
Batch Size
If the batch mode is set to every batch the batch size can be specified.
Expression Editor Request Tokens

Many of the properties on this page can be set as either a text merged field or an expression. Properties default to text merge mode, but can be changed to expression mode in the Expression Editor. The expression editor for a property can be launched by clicking the fx button.  The expression editor has some predefined tokens:

BatchNumber
The number of the batch that is about to be sent. On the first request it will have a value of 1.
MergedBody
This is the merged document that was created for the current batch.
CurrentItem
This token is only available in the item key field on the output columns page.  It represents an item in the array of response items.
Expression Editor Text Merge Mode

SSIS JSON Destination - Expression Editor Text Merge Mode

In text merge mode, property values will be whatever text was entered, but with any variables or special tokens replaced with their actual values before each HTTP request is sent.

Expression Editor Expression Mode

SSIS JSON Destination - Expression Editor Expression Mode

In expression mode the property is set to an expression which is evaluated before each request to determine the value of the field. Expressions can contain variables and special tokens, but also functions and conditional logic. The functions in the Value Lookup Functions category may be particularly useful for this component, as they can be used to look up values and JSON documents and HTTP headers.

Merged JSON

The merged JSON page allows your incoming columns to be merged into a JSON document. It contains three subpages - Design, Input Columns and Advanced

Design

Define the relationship of the Inputs in the Design Page.

SSIS JSON Destination - Merged JSON - Design

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.

Input Columns

Configure column/node specific merge settings.

SSIS JSON Destination - Merged JSON - Input

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

Configure JSON and merge related settings here.

SSIS JSON Destination - Merged JSON - Advanced

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.

Output Columns

Output columns can be configured on this page. The values of output columns will be extracted from the response of any HTTP requests that are sent. Output columns are typically used to get success or Id information about items you were created/updated or deleted on the server.

SSIS JSON Destination - Output Columns

Response Is Array
Check this property if the HTTP response will contain an array with items corresponding to your input rows.  If the response does not contain information about each input item you wouldn't need to check this property.
Response Collection Base Path
If your response contains an array, the response collection base path property will be the JPath that will return the items in the array.
Response Item Lookup Strategy
If your response is an array a strategy must be selected to match input rows to response array items.
  • Sequential Strategy - If this strategy is selected it is assumed there are an equal number of input rows and output array items, and they are matched in the order they appear. 
  • ItemLookup strategy - Use this strategy when the output array items are not in the same order as the input rows.  Selecting this strategy will allow you to define an item key below which will determine how input rows are matched to output array items.
Item Key
If the item lookup strategy is used an item key expression must be defined.  This expression will be evaluated for each input row in the batch and each array item in the response and should return true for a match and false when they are not a match.  The CurrentItem token is available in the expression editor and will allow a value from an array item to be easily compared to a column value to match rows and response array items.
Output Columns Grid
The output columns grid contains any output columns.  Output column values will always come from the HTTP response.  If the response is not an array the relative path should be the full JPath to the output value in the HTTP response.  If the response is an array the relative path will be return the value at the specified JPath for the response array item that has been matched to the current output row.
HttpBody Checkbox
Checking this box will add an output column that contains the http body of the response.
HttpResponseCode Checkbox
Checking this box will add an output column that contains the http response code of the response.
HttpResponseCodeName Checkbox
Checking this box will add an output column that contains the http response code name of the response.

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.

SSIS JSON Destination - Error Handling

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.

SSIS JSON Destination - Error Handling Flow