By: Ray Barley | Last Updated: 2009-05-28 | Comments (12) | Integration Services Data Flow Transformations
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?
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.
- 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.
Last Updated: 2009-05-28
About the author
View all my tips