Documentation forTask Factory

Task Factory Unpack Data Transform

 Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.

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.

Transform Icon Transform Description
Task Factory Unpack Data Transform Icon The 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

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

  • Delimited
  • JSON
  • XML

Delimited Properties

Task Factory Unpack Data Transform Delimited Properties

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

Json Properties

Task Factory Unpack Data Transform Json Properties

Option Description
Root Json Path Json queries can return multiple levels, therefore, this field Identifies the root to be used.
Unpacked Data Columns Users 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",
               }
                               ]
               }
       ]
}

Deserialization Settings

The following deserialization settings are available:

Option Description
Date Parse Handling Select the Date Parse Handling Option:
NoneNo active Date Parse handling. 
DateTimeConverts the date and time string to the DateTime format.
DateTimeOffsetConverts the date and time string relative to UTC in the DateTimeOffset format.
Additional Information: For more information about Date Parse Handling options see the following MSDB articles:


Date Time Zone Handling Select the DateTime zone handling option:
LocalAdds an object that represents the local time zone.
UtcAdds an object that represents UTC.
UnspecifiedNo specified DateTime zone handling.
RoundtripKindConverts the DateTime object to a string with the format specifier and then converts back to DateTime.
For more information about DateTime zone handling options see the following MSBD articles:

Float Parse Handling Select the Float parse handling option:
DoubleFloat parse as double.
DecimalFloat parse as a decimal.

XML Configuration

Task Factory Unpack Data Transform Xml Properties

Option Description
Root XPath Query Xml 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 Columns Users 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 Columns

Task Factory Unpack Data Transform Test Unpack

Option Description
Enter Text To Be Used To Test The Unpack Columns Users can copy and paste a row of packed data into this field to test the component's configuration and output.
Run Test Executes 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.