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.
The script has the following parameters:
- DataColumn - specifies the column containing the text with array.
- 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.
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
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.