SSIS – Unpivot Transformation

I have worked on Unpivot transformation many times with new experience every time. I used to forget the Unpivot property options every time so plan to document different practical options with Unpivot on source data updates.

Test Cases

1. Unpivot – Standard development
2. Unpivot – Additional options setup on source data length changes
3. Unpivot – Sort order of Unpivot output columns

Test Case 1: Unpivot – Standard development

Here developing package with Unpivot transformation with two data flow components with two Flat File Connections

1. Unpivot 1
2. Unpivot 2

 

SSIS - Package

 
Unpivot 1 source file data as below
 

Unpivot 1 - Source data

 
Options setup on Unpivot component as below
 

Unpivot options setup

 
Details of points in the setup as

Point 1: Pass Through

While checking only the pass through option on input columns, the input columns only pass through the component but not involved in any type of transformations. Here Product No and Price columns are “pass through”

Point 2: Unpivot columns

Checking the source columns to Unpivot from source, here Product Name and Description are checking for Unpivot and upon checking this option, these two columns becomes rows at target but we also need to setup target column Name to hold these source values (Point 4)

Point 3: Pivot Key Value

On converting the source columns from point 2 to rows values, the values existing at source columns in these two columns also needs to assign a new column. Here Destination column name holds the values of source columns data.

Point 4: Pivot key value column name

Pivot Key Value column name is the target column name to hold the source Unpivot column names.

Executing package and used data viewers
 

Unpivot 1 Execution

 
Source data has converted into Unpivot format as below.
 

Unpivot result

 

Test Case 2: Unpivot – Additional options setup on source data length changes

It is very common to update the source data in data warehouse environments, specifically from business analyst perspective. Upon changing the source data, we expect the Unpivot package will fail in the some case. Here we updated source column Description as shown below.
 

Source file - Updated

 
After updating the source data file, just ran the Unpivot package, the package failed as below with an error message.
 

Source component - Failure

 
Error Message:
 

Error: 0xC02020A1 at DF – Unpivot 1, FF_SRC Unpivot 1 [14]: Data conversion failed. The data conversion for column “Description” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.  Error: 0xC020902A at DF – Unpivot 1, FF_SRC Unpivot 1 [14]: The “output column escription (31)” failed because truncation occurred, and the truncation row disposition on “output column “Description” (31)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  Error: 0xC0202092 at DF – Unpivot 1, FF_SRC Unpivot 1 [14]: An error occurred while processing file “C:\Users\prav\Documents\BIpassion\SSIS – Unpivot\Unpivot 1.csv” on data row 2.  Error: 0xC0047038 at DF – Unpivot 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “FF_SRC Unpivot 1” (14) returned error code 0xC0202092.

 
Reason and solution:
 
The default length of flat file source component for all the columns is 50, but here the udpated description length is more than 50, it is raising an error of truncation to source column.

The solution is to manually change the target column length to 500 (considering length 500 is the maximum length for the column) at advanced properties of flat file source connection and refreshing the flat file soource component to get the latest changes from connection changes as shown below.

Updated Source data length

 
Unpivot consideration:
 
On updating the length of the description column, Unpivot component will show the error symbol with error information as below.
 

Unpivot Error

The error message 2 is clearly pointing to the meta data of the Unpivot columns even though we haven’t change any column names.

Unpivot will consider data length of Unpivot columns as meta data, so the updated length of Description length 500 and existing Product Name length 50 so these two Unpivot columns are not of same length.

When using Unpivot columns, all the columns needs to have same length so we need to updated the column length of Product Name length to 500 to make equal length of all Unpivot columns.
 

Updating the length property

Update length property

 
After updates, the package executed with success with below result
 

Updated result

 
Test Case 3: Unpivot – Sort order of Unpivot output columns

In order to simulate the output sort order of Unpivot columns, I have created second data flow with Unpivot 2 flat file connection. Actually we could also observe this in the first example as well. In order to get a quick catch of the point, I considered another scenario.

Flat File Source data file as below.

Unpivot 2 - Source data

 
There are no changes in the setup and executing the package, the output Unpivot columns will go in alphabetical ascending order, we could see this consideration at point 2 as below.
 

Unpivot 2 result

 
 
Considerations:

1. I have used a small amount of source data to simulate the different options of Unpivot component.

2. I haven’t taken the destination component to insert the records into a table.

3. Unpivot component will give the output columns in Unicode string [DT_WSTR], the output column needs to transform to [DT_STR] in the case of OLE_DB Destination.

4. Unpivot may have another issues to like consider when Unpivot NULL values from source and alias name to Unpivot columns.

5. Finally, these points are only on my experience with Unpivot usage but not sure any other considerations. It all depends on the source data and desired output data according the solution development.