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 the column containing the text with array.
- 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.
- 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.
- 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.
Handling different type of input format
The default transformation function expects JSON array as 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.
Quick Start
In this quick-start we will setup a JSON Transformation.
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.
In this quick-start we will setup a XML Transformation.
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.
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.

Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.