Better Know A SSIS Transform – The Pivot Transform

This is part 1 of my 29 part series called Better Know A SSIS Transform.  Hopefully you will find the series informative.  I will tell you a little about each transform and follow it up with a demo you can do on your own.

Pivoting is a common business practice to gain a better visualization of company performance.  Basically the purpose of pivoting is to changing rows into columns.  So if you want to display sales across all months you would use pivoting to turn a single date column with the month into 12 columns with all the months listed.  You can accomplish this in TSQL or using the Pivot Transform in SSIS.

When you first try using the Pivot Transform you may be a little intimidated.  This transform is not as easy to configure as many of the other Data Flow Transforms.  It sends you straight to an Advanced Editor and you can't just click a couple boxes to complete it's configuration. 

My goal is to hopefully demystify using the Pivot Transform so those who have a real need to pivot data can accomplish that inside a SSIS package.  For this example I will be using the AdventureWorksDW2008 database.

Example Overview

•   Use an OLE DB Source to bring in data from AdventureWorksDW2008 database
•   Data Viewer between source and Pivot Transform to see data before pivot.
•   Pivot Transform to pivot on day of week column
•   Another Data Viewer to see data after Pivot Transform has perform it's operation
•   Union All is used just to test and not actually send data anywhere.  Just a trash destination.

Step 1: Configure Source

•   Use the following query to return the result set used for this demo:

SELECT p.EnglishProductName AS ProductName,

SUM(f.OrderQuantity) AS OrderQuantity,

d.EnglishDayNameOfWeek AS DayofWeek

FROM FactInternetSales f INNER JOIN

DimProduct p ON f.ProductKey = f.ProductKey INNER JOIN

DimDate d ON f.OrderDateKey = d.DateKey

GROUP BY p.EnglishProductName, d.EnglishDayNameOfWeek, d.DayNumberOfWeek

ORDER BY p.EnglishProductName, d.DayNumberOfWeek

Step 2: Add Data Viewer

•   Drag over a Pivot Transform and connect the Source to it.
•   Right-click on the Data Flow Path between the Source and the Pivot Transform to open the Data Flow Path Editor.
•   Select Data Viewers then click Add and OK to add a Grid Data Viewer.  Click OK once more to return to the Data Flow

Step 3: Add Input Columns

•     Open the Pivot Transform and select all columns on the Input Columns tab

Step 4: Configure Input Columns

  Expand the Pivot Default Input and Input Columns

•   Select ProductName and change the PivotUsage to 1
•   Select DayofWeek and change the PivotUsage to 2
•   Select OrderQuantity and change the PivotUsage to 3
•   The different PivotUsage code are the following:
  • 0 - is a column that is just passed through without any changes
  • 1 - is a column that is a set key.  All input rows with the same set key are combined into one output row.
  • 2 - is the column to pivot.
  • 3 - values from these columns are placed in pivot columns.

Step 5: Configure Output Columns

•   Unfortunately most of these steps require a lot of manual work. 
•   Expand the Pivot Default Output and Output Columns
•   Click Add Column until you have the expected number of output columns.  In this example 8 columns
•   Rename the first column ProductName and change the SourceColumn property to match the LineageID from the input ProductName column.  Your LineageID will likely be different then mine.
•   Next, rename the rest of the columns to the days of the week.
•   On these columns the change the SourceColumn to match LineageID to the input OrderQuantity.  This may not sound right but remember the data in these date columns will display the OrderQuantity for each day of the week.
•   Last, only on the day of week columns change the PivotKeyValue (This should be left blank for ProductName to match the name (Ex. PivotKeyValue = Sunday)

Step 6: Add Data Viewer and Destination

•   Drag over a Union All (if you are just testing) or an actual destination and connect the Pivot Transform to it.
•   Right-click on the Data Flow Path between the Source and the Pivot Transform to open the Data Flow Path Editor.
•   Select Data Viewers then click Add and OK to add a Grid Data Viewer.  Click OK once more to return to the Data Flow