Script JSON Transformation
Overview
Data flow transformation to retrieve data from column containing array with JSON objects.
Setup
The script has the following parameters:
- DataColumn - specifies column containing JSON array.
- Transform - specifies transformation function to transform JSON array before output. underscore.js functions are also available for use by default. The parameter of this function is the whole JSON array. To quickly test the transformation function can be used one of the online JavaScript Editors - JS Bin. Here is the example transformation where "Price" is multiplied by the exchange rate.
- IncludeSequenceTerminator - specifies if an empty row will be added to output after the processing of an input row. This is useful when input rows contain arrays of multiple JSON objects, e.g. in the context of SSIS+ support for composite records.
- Sample - sample JSON 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 created again.
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.
Configuration
To use this script, you would need to load it in COZYROC JavaScript Component. You can download the JavaScript file and browse to it via the "Import JavaScript code" button.
JSON_Transformation.jsReady to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.