SSIS: Using the UNPIVOT component

Recently I came across a data set that had been denormalized, but when we wanted to report on it dimensionally we needed to do an unpivot to get the data in the right structure for our fact table. Fortunately SSIS has a command for just this exact purpose, called UNPIVOT. When you first drag it over, it can take a minute to get a feel for the component, but it’s actually pretty straightforward. To demonstrate the functionality, I’m just going to use a few simple tables that I created. The first part of the script creates our denormalized table and inserts some data. The second create statement creates the new table we’re loading data into:

 

CREATE TABLE dbo.UnPivotExample
    (ID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,Person1 int NULL
    ,Person2 int NULL
    ,Person3 int NULL
    ,Person4 int NULL
    ,Person5 int NULL
    )
GO
INSERT INTO UnPivotExample VALUES (4,3,5,4,4)
INSERT INTO UnPivotExample VALUES (4,1,5,5,5)
INSERT INTO UnPivotExample VALUES (4,3,5,4,4)
INSERT INTO UnPivotExample VALUES (4,2,5,5,4)
INSERT INTO UnPivotExample VALUES (5,1,5,5,5)

GO

CREATE TABLE dbo.UnPivotResult
    (ID int NOT NULL
    ,SalesPerson char(7) NULL
    ,Amount int
    )
GO

Now that we have our table, our result in management studio looks like the below:

image

 

Next we can start creating our package. I created a package, and dragged a data flow task over. Then I’m creating a data source to point to the table I created above, and directly underneath I dragged over the unpivot component. In SQL Server Data Tools, the UNPIVOT component is under the “Other Transforms” section:

image

And my initial data flow looks like:

image

When I open up the Unpivot component, the configuration screen appears. In this example, we’re taking the sales people from each column and combining the amounts of their sales together. The Pivot Key value column name is the name we want to have the columns combined into. My resulting component when I’m done with my configuration looks like the below:

image

 

An item of note here is that the pivot key value column name is output as a unicode string. If your column is a unicode string you’re fine, but if your new column you’ve created is non-unicode you’ll want to put a data conversion component to convert the column. I added my data conversion, and my resulting destination table (the UnPivotResult table from above) and my final data flow looks like:

 

image

After I run it I can see it management studio, and my table looks like:

image

 

And that’s it. The Unpivot component is easy to set up, and very helpful for flipping data into 1 column from multiple when we need. As an added bonus, it’s faster than trying to use the unpivot command in t-sql directly in the source before we bring it into SSIS.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s