Task Factory Unpack Data Transform

Unpack Data Transform

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
	The Unpack Data Transform is used to output delimited, XML, or Json data contained within a single column source.

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

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

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: None No active Date Parse handling.  DateTime Converts the date and time string to the DateTime format. DateTimeOffset Converts 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: DateTime DateTimeOffset Struct
Date Time Zone Handling	Select the DateTime zone handling option: Local Adds an object that represents the local time zone. Utc Adds an object that represents UTC. Unspecified No specified DateTime zone handling. RoundtripKind Converts 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: TimeZoneInfo Class (Local and UTC) DateTimeStyles Enum (RoundtripKind)
Float Parse Handling	Select the Float parse handling option: Double Float parse as double. Decimal Float parse as a decimal.

XML Configuration

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

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.