UNVIPOT TRANSFORMATION WITH A COMBINATION OF SINGLE AND MULTIPLE DESTINATION COLUMNS

First published on MSDN on May 21, 2014

It is highly not uncommon that while using Unpivot Transformation in SSIS you feel the need to unpivot the columns to multiple destination columns along with single destination column. Dealing with them individually is easy but together is a little complicated when you think about it but that isn’t the case.

Let’s take an example:

You have a source table like this:

You want your destination table to look this this:

How would we normally design our package keeping in mind that we need to unvipot columns into multiple destination columns:

When you design the package as above, you would get the error:

“PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.”

A simple reason to it is that you cannot have multiple destination columns and single destination columns in the same unpivot transformation task. In a broader way, you cannot have different set of unpivoted destination columns in the same task.

Let us now see how we can overcome this hurdle.

We need to have different unpivot transformation task for different set of unpivoted destination columns required. Pass through the values and use them in the further unpivot transformation task as it meets your requirement.

As in our example:

We need to modify the package and have it as below:

Unpivot Transformation Task 1 Configuration (Multiple Destination columns):

Unpivot Transformation Task 2 (Single Destination Column):

As our final aim was to move all the B’s into B, keeping in mind that A and B are the multiple destination columns we used the available A’s and B’s pair (pair in the sense, having a common pivot key value) and we combined these paired B’s with the left over B’s and move them to the destination by having a separate unpivot transformation task for it.

Let us do some mathematics here: Calculation of number of rows which get loaded into the destination-

From the source we extract: 3 rows

After the Unpivot Transformation Task 1, we have 3 combination of A’s and B’s and so the number of rows we get after the execution of this task is: 3*3 = 9 rows.

After the Unpivot Task 2, we have 2 left columns of B’s, i.e. B4 and B5 and we had the Known history being passed through. So in total we have 3 columns, thus the number of rows being successfully loaded in the destination: 9*3 = 27 rows.

An alternate solution to this referring to our example is to have A4 and A5 with all null values and have just a single unpivot task and have all the A’s have their destination column as ‘A’ and all the B’s have the destination column as ‘B’. This solution would be feasible when you have a key -> value kind of relationship as the destination table will have null values for the key’s which do not have an associated value in the source. The former solution is feasible when you have the values not associated with a single key and associated with all the keys. If we use the alternate solution, we’ll have 3*5 = 15 rows (3 being the number of source rows and 5 being the pairs of A’s and B’s).

Enjoy Unpivoting!!

Author : Deepak(MSFT), Suport engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft