How To Use the Unpivot Data Flow Transform in SQL Server Integration Services SSIS

By: Ray Barley   |   Read Comments (9)   |   Related Tips: More > Integration Services Data Flow Transformations

Problem
I have some sales forecast data that I get from the business users in an Excel spreadsheet.  I need to load this data into a SQL Server database table.  The forecast contains product categories on the rows and the sales forecast for each month is on the columns.  I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task.  How can I transform this data into a table that has Category, Month and Sales Forecast columns? 

Solution
SSIS has a Data Flow Transformation called Unpivot which can do exactly what you need.  Let's assume that your spreadsheet looks like this:

Although not shown above, there are 12 columns for forecast data, January through December.  Assume we want to load the data from the Excel spreadsheet into the following table:

CREATE TABLE [dbo].[SalesForecast](
 [ForecastDate] [datetime] NULL,
 [SalesForecast] [int] NULL,
 [CATEGORY] [nvarchar](255) NULL
)

We'll create a simple SSIS package to process the Excel spreadsheet.  The package will have the following Data Flow:

The following are the main points about the above data flow:

• The Excel Source reads in the Excel spreadsheet.

• The Unpivot transform takes the forecast value columns and transforms them into rows.

• The Script Component takes the column names (i.e. JAN, FEB, etc. which are also transformed from columns to rows) and prepends the ForecastYear package variable to create a string value with the format of YYYY-MM-DD.  This can be inserted into a column of type DATETIME.

• Insert Into SalesForecast is an OLE DB Destination that inserts rows into the SalesForecast table.

Before we start the explanation of the Unpivot, let's add a data viewer after the Unpivot and run the package.  The data viewer allows us to see the contents of the data flow.  To add a data viewer, right click the line connecting the Unpivot and Script Component, select Data Viewers, Add, then Grid.  The data viewer output is shown below:

As you can see the Unpivot component has performed the transformation that we need, taking the Sales Forecast values in the columns and turning them into rows.

The Unpivot Transformation Editor is shown below:

The Available Input Columns grid contains the list of columns in the data flow as read from the spreadsheet.  The CATEGORY column has Pass Through checked which means the column value simply passes through this component unchanged.  You can see the CATEGORY column in the data viewer output above.  The columns that have the checkbox to their left checked are unpivoted; i.e. these columns become rows.  All of the checked input columns are being transformed to the SalesForecast column, one per row, as shown in the Destination Column above.  Referring back to the data viewer output, you can see the SalesForecast column.  The Pivot key value column name is a new column that is added to the data flow; the value of this column is specified in the Pivot Key Value column.  The Pivot Key Value allows you to specify the value of your choice for each column in the original spreadsheet.  The Pivot Key Value shown is the first day of the month specified in the Input Column.  You can see the MonthDay column in the data viewer output above.

The Script Component has the following single line of code that prepends the package variable ForecastYear to the MonthDay column in the data flow to assign a string value to the output column ForecastDate that can be implicitly converted to a DATETIME:

        Row.ForecastDate = _
            Me.ReadOnlyVariables("ForecastYear").Value.ToString() + _
            "-" + Row.MonthDay

To test executing the package, use the following DTEXEC command line, specifying a value for the ForecastYear package variable (add the appropriate path before the package name):

DTEXEC /f unpivot_sample.dtsx /set \package.variables[ForecastYear];2010

Note that there is a semi-colon separating the package variable and the value.  Also the variable name (ForecastYear) is case-sensitive.

Next Steps

• It's quite common to get data that needs to be transformed similar to this example, especially when the data is created by business users in an Excel spreadsheet.  The Unpivot data flow transformation can be used to simplify this task.
• Download the sample SSIS package and experiment with it. 

About the author

Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips

We Recommend

• More Business Intelligence Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code