A question on the SSIS forum earlier today asked:

I need to dynamically unpivot some set of columns in my source file. Every month there is one new column and its set of Values. I want to unpivot it without editing my SSIS packages that is deployed

Let’s be clear about what we mean by Unpivot. It is a normalisation technique that basically converts columns into rows. By way of example it converts something like this:

AccountCode	Jan	Feb	Mar
AC1	100.00	150.00	125.00
AC2	45.00	75.50	90.00

into something like this:

AccountCode	Month	Amount
AC1	Jan	100.00
AC1	Feb	150.00
AC1	Mar	125.00
AC2	Jan	45.00
AC2	Feb	75.50
AC2	Mar	90.00

The Unpivot transformation in SSIS is perfectly capable of carrying out the operation defined in this example however in the case outlined in the aforementioned forum thread the problem was a little bit different. I interpreted it to mean that the number of columns could change and in that scenario the Unpivot transformation (and indeed the SSIS dataflow in general) is rendered useless because it expects that the number of columns will not change from what is specified at design-time.

There is a workaround however. Assuming all of the columns that CAN exist will appear at the end of the rows, we can (1) import all of the columns in the file as just a single column, (2) use a script component to loop over all the values in that “column” and (3) output each one as a column all of its own. Let’s go over that in a bit more detail.

I’ve prepared a data file that shows some data that we want to unpivot which shows some customers and their mythical shopping lists (it has column names in the first row):

We use a Flat File Connection Manager to specify the format of our data file to SSIS:

and a Flat File Source Adapter to put it into the dataflow (no need a for a screenshot of that one – its very basic).

Notice that the values that we want to unpivot all exist in a column called [Groceries]. Now onto the script component where the real work goes on, although the code is pretty simple:

Here I show a screenshot of this executing along with some data viewers. As you can see we have successfully pulled out all of the values into a row all of their own thus accomplishing the Dynamic Unpivot that the forum poster was after.

If you want to run the demo for yourself then I have uploaded the demo package and source file up to my SkyDrive: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20100529/Dynamic%20Unpivot.zip

Simply extract the two files into a folder, make sure the Connection Manager is pointing to the file, and execute!

Hope this is useful.

@Jamiet