Using the JSON Extract Component

The JSON Extract component does something similar to the JSON source component, but it is a transformation component which receives JSON documents from an upstream component, extracts data from the received JSON documents and produces column data for the SSIS pipeline. 

Design Page

This page allows you to import an example JSON document to generate the expected hierarchy of incoming JSON documents. From there you can add and remove nodes manually to refine the structure.

JSON Extract Editor

Once you've added some nodes by either importing or manually adding, you may need to refine the structure by editing the node properties. The following properties will have an effect on the extract process:

  • JSON Name: This is the name of the node as it is in the expected JSON. Notice when this property is changed the JPath and Data Name properties are automatically updated.
  • Data Name: This is the name of the Output (green rows) or Column (yellow rows). Notice that some rows do not have a Data Name property. This is because these are nodes that define hierarchy and have no immediate value to extract. These nodes are important however because it affects the JPath of child nodes.
  • Type: There are 3 different JSON node types to choose from:
    • Array: This node contains a list of nodes. Notice the JPath of the child elements will contain a '[n]' at the end. This means this node repeats.
    • Object: This node often just defines hierarchy and is simply a node that contains nodes.
    • Value: This is a basic node containing a value available for extraction.
  • JPath: This property is not editable but instead generated based on the above properties.
Import Settings Button

Click Import Settings button to import the configurations from a file.

Export Settings Button

Click Export Settings button to save the configurations of the component to a JSON file later for reuse.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon

Click the Generate Documentation Icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

General Page

Configure Input and Output settings.

JSON Extract Editor - General Page

JSON Column

Specifies the input column that contains the JSON to extract.

Null Mode

Specifies what represents a NULL value in the JSON. There are 2 options:

  • Null Value: If a node cannot be found or is null then the value is NULL.
  • Empty String: If a node cannot be found or is empty then the value is NULL.

In the Outputs data grid, you can check and uncheck outputs to specify if you wish to have these outputs in your component.

  • Output Name: The name of the output. This property is configurable.
  • Key Field: This specifies the identifier column for this output. This is important for linking outputs with each other. By default, the Key Field is set to '_RowIndex' which is a special field that contains the current count of this output node. This is useful because many times a JSON object will have no key field because relationships are defined hierarchically. During runtime, the value of this field will go into the _ParentKeyField field of all of this output's children. This property is configurable.
  • JPath: The JPath of the output. This property is not editable.

Columns Page

Configure column settings for each output.

JSON Extract Editor - Columns Page

Select the output whose columns you wish to configure in the top left combo box. Select a column in the data grid on the left to populate its properties in the property grid on the right.

The Columns Page grid consists of:
  • Column Name: Column that will be retrieved.
  • Data Type: The data type of this column.
  • Properties window for the field listed
    • Column Type: Description of the selected column.
    • Name: Specify the column name.
    • CodePage: Specify the Code Page of the column.
    • Data type: The data type can be changed according.
    • Length: Specify the Length of the columns. 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: Specify the number of digits in a number.
    • Scale: Specify the number of digits to the right of the decimal point in a number.
    • JPath: The JPath of this column.

There are a couple of special columns to take note of:

  • _RowIndex: This column contains the current count of this output node. 
  • _ParentKeyField: This column contains the value of this records parent key field.

Error Handling

This page allows you to specify how errors should be handled when they happen.

JSON Extract Editor - Error Handling Page

There are 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 Extract Editor - Error Output