NEW: Check our new hosted service COZYROC Cloud ...
Script JSON Transformation
Overview
Data flow transformation to extract structured data from a text column, storing arrays with objects in JSON, XML or other text format. An optional transformation function is used to convert raw array to JSON array.
Setup
The script has the following parameters:
- DataColumn - specifies columns containing the input data. When multiple columns are selected, the content parameter passed to the Transform function will be a JavaScript object where each selected input column will be configured as an object member.
- Sample - sample data to initialize metadata. It would be used for the creation of output columns. If the sample is changed the output columns will be deleted and recreated.
- Transform - specifies transformation function to create JSON array before output. underscore.js functions are also available for use by default. The parameter of this function is the whole input array. To quickly test the transformation function, one of the online JavaScript Editors - JS Bin can be used. Here is the example transformation where "Price" is multiplied by the exchange rate. When a single input column is selected the contents are passed without modification. The function should parse the input. When multiple columns are selected the script tries to parse the data, if not possible the data is passed unchanged. All columns data is passed as a JavaScript object where property names are the column names and values containing the column data.
- IsSequence - specifies if the result is a single record or multiple. When a single record is selected the output is synchronous, otherwise asynchronous.
- IncludeSequenceTerminator - specifies if an empty row will be added to the output after the processing of an input row. This is useful when input rows contain arrays of multiple objects, e.g. in the context of SSIS+ support for composite records.
Handling different types of input format
The default transformation function expects JSON array as a string. If your input is different, you can customize the transformation function according to your needs. Please check the QuickStart(s) below for further information.
Demonstration
Quick Start
In this quick-start we will setup a JSON Transformation.
Selecting component type
Let’s start by creating a transformation JavaScript Component. After dropping the component on the design surface, let’s keep the selected "Transformation" radio button. This would create a component with one input and one output.
Connect component input
The JSON Transformation script has input column parameter. To select an input column, the input needs to be connected.
Importing script code
Press "Import JavaScript code from file" button (bottom left edge) and select the downloaded "JSON Transformation.js" file. The script code will be replaced with the file contents.
Go to the "Parameters" tab
After you switch to the "Parameters" tab you would see the parameters in the property grid there. Until you specify values for "DataColumn" and "Sample", there would be two validation error messages that prompt you to do so.
Selecting "DataColumn"
After selection of a "DataColumn", the selected virtual input column is used immediately as read-only. If the column is changed the previously used column is removed and the newly selected is used.
Providing sample data
The format of the sample data is array of JSON objects. All objects are processed and from their properties the output columns are created. The columns are created immediately after changing value of the parameter. If "Sample" is changed the current output columns are deleted and the new are created.
Metadata
Metadata changes can be seen in the tab "Properties", page "Metadata". The input column marked with blue is created after the selection of the "DataColumn" parameter. The output columns marked with red are created after the parameter "Sample" is specified.
Everything is ready
The component setup is done. The only thing left is to press the Editor "OK" button.
In this quick-start we will setup a XML Transformation.
Selecting component type
Let’s start by creating a transformation JavaScript Component. After dropping the component on the design surface, let’s keep the selected "Transformation" radio button. This would create a component with one input and one output.
Connect component input
The JSON Transformation script has input column parameter. To select an input column, the input needs to be connected.
Importing script code
Press "Import JavaScript code from file" button (bottom left edge) and select the downloaded "JSON Transformation.js" file. The script code will be replaced with the file contents.
Go to the "Parameters" tab
After you switch to the "Parameters" tab you would see the parameters in the property grid there. Until you specify values for "DataColumn" and "Sample", there would be two validation error messages that prompt you to do so.
Selecting "DataColumn"
After selection of a "DataColumn", the selected virtual input column is used immediately as read-only. If the column is changed the previously used column is removed and the newly selected is used.
Providing transformation function
Here we need transformation from XML to JSON.
We use the following expression:
The array in XML is located in property "person" of the "root" XML object, so we return this property.
Providing sample data
The format of the sample data is XML text. The XML is converted to JSON and only the array is returned. This is done by the transformation function. The sample data is in XML format because the input data is also in XML format. After the JSON array is returned from transformation all objects are processed and from their properties the output columns are created. The columns are created immediately after changing value of the parameter. If "Sample" is changed the current output columns are deleted and the new are created.
Sample data used:
XML elements with name person will be converted to JavaScript Array.
Metadata
Metadata changes can be seen in the tab "Properties", page "Metadata". The input column marked with blue is created after the selection of the "DataColumn" parameter. The output columns marked with red are created after the parameter "Sample" is specified.
Everything is ready
The component setup is done. The only thing left is to press the Editor "OK" button.
Configuration
To use this script, you would need to load it in COZYROC JavaScript Component. If you are using COZYROC SSIS+ 2.0 or later, after selecting the corresponding script type and opening the component editor, you can select the script from a dropdown list with the pre-built scripts. For COZYROC SSIS+ 1.9, you can download the JavaScript file and browse to it via the "Import JavaScript code" button.
JSON_Transformation.js
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.