Task Factory Unpack Data Transform

Important:  Unpack Data is available for SQL versions 2012 and higher.

As of Feb 2, 2018, DT_EMPTY, DT_NULL and all datatypes that included BYREF have been removed. Any package configured to use these datatypes will error and have to be reconfigured.

Unpack Data Transform

Transform IconTransform Description
Task Factory Unpack Data Transform IconThe Unpack Data Transform is used to output delimited, XML, or Json data contained within a single column source.

Task Factory Unpack Data Transform File Format

OptionDescription
Column With Packed DataSelects the column that stores the delimited or XML data to be unpacked.
 Input Columns To Include In Unpacked OutputSelects other columns to be included in the Unpacked Output (non-selected columns continue down the standard output.)
Packed Data FormatSelects the format of the packed data . Data must be one of the following:

  • Delimited
  • JSON
  • XML

Delimited

Delimited Configuration 

Task Factory Unpack Data Transform Delimited Properties

OptionDescription
Data Contains Headers?Similar to the native Flat File Source, this selection identifies the first row as containing column headers.
Row DelimiterIdentifies a character or carriage return (\n) to signify a new row.
Column DelimiterIdentifies the character used to separate values for the different columns such as a comma.
Text QualifierIdentifies the character used to wrap values such as quotation marks.
Unpacked Data ColumnsUsers can create, remove, and configure the name, index (zero-based), data type, length, precision, and scale of the columns being extracted or unpacked.
Json

Json Configuration

Task Factory Unpack Data Transform Json Properties

OptionDescription
Root Json PathJson queries can return multiple levels, therefore, this field Identifies the root to be used.
Unpacked Data ColumnsUsers can create, remove, and configure the name, index (zero-based), data type, length, precision, and scale of the columns being extracted or unpacked.

Using Wildcards (*)

The Unpack Data Transform allows for wildcards when arrays are returned. 

Example:
Using the Json Results below, the Root Json Path would be results[0].appInventory[*]. This information tells the component to use the array found at position 0. Because some child arrays do not have an object name, a wildcard or can be used to return all child array objects.

{
   "results": [
       {
           "appInventory": [
               {
                   "name": "Sample name 1",
                   "identifier": "123",
               },
               {
                   "name": "Sample name 2",
                   "identifier": "456",
               },
               {
                   "name": "Sample name 3",
                   "identifier": "789",
               }
                               ]
               }
       ]
}
XML

XML Configuration

Task Factory Unpack Data Transform Xml Properties

OptionDescription
Root XPath QueryXml queries can return multiple levels, therefore, this field Identifies the root to be used.
Namespaces:
  • Prefix - Identifies the prefix used in an xml namespace. 
    • Example: In the element <SalesDetails xmlns:url="http://www.site.com"> the prefix is url.
  • Uri - The Url or address identified in the namespace. In the example above, http://www.site.com is the uri that should be configured.
Unpacked Data ColumnsUsers can create, remove, and configure the name, index (zero-based), data type, length, precision, and scale of the columns being extracted or unpacked.

Test Unpack

Test Unpack Columns

Task Factory Unpack Data Transform Test Unpack

OptionDescription
Enter Text To Be Used To Test The Unpack ColumnsUsers can copy and paste a row of packed data into this field to test the component's configuration and output.
Run TestExecutes a test based on the component's configuration and data pasted into the window above. Results display in the window below.

Additional Information:  See the Task Factory Error Row Handling article for more information about this functionality.