Pivot transformation in SSIS allows you to perform a pivot operation on your input data. Here, we are going to explain pivot transformation in SSIS 2008R2 using a simple example so that you can understand it better.
Please refer to Pivot in 2014 article to understand the changes they made.
Pivot Transformation in SSIS 2008 R2
Scenario: We want to PIVOT the below table using Pivot transformation in SSIS 2008 R2 for the desired output.
Open BIDS Drag and drop the data flow task from the toolbox to control flow, and rename it as pivot transformation in SSIS 2008 r2.
Double click on it, and SSIS will open the data flow tab.
Drag and drop OLE DB Source, Pivot transformation, and OLE DB Destination from the toolbox to the data flow region
Double click on the OLE DB source in the data flow region will open the connection manager settings and provides space to write our statement.
SQL Command we are going to use as our SSIS Pivot Transformation source is:
SELECT PROD.ProductID,
PROD.Name,
YEAR(OrdHead.OrderDate) as OrderYear,
SUM(Details.OrderQty) AS OrderQuantity
FROM Sales.SalesOrderDetail AS Details
INNER JOIN
Production.Product AS PROD ON
Details.ProductID = PROD.ProductID
INNER JOIN
Sales.SalesOrderHeader AS OrdHead ON
Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY PROD.ProductID,
PROD.Name,
YEAR(OrdHead.OrderDate)
ORDER BY PROD.Name,
YEAR(OrdHead.OrderDate)
Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.
Click OK and edit the Pivot Transformation in SSIS 2008 R2. In the component properties tab, we can change the name, but we will leave it as it is for now
Go to the input columns tab and select all input columns that we received from the OLE DB Source
Go to the SSIS Pivot Transformation input and output properties tab, and select pivot default input. Under input columns, you will find all the columns which you selected in the previous tab. The only property you must set for each input column is the PivotUsage.
It describes the values you can use in the PivotUsage property:
| OPTIONS | DESCRIPTION |
|---|---|
| 0 | Column values will pass through the transformation |
| 1 | Set key. All the columns with the same set key |
| 2 | This is a Pivot column (These Column values become the Column names) |
| 3 | Values from this column are placed in the new columns created by the Pivot Transformation |
So, in this example, the PivotUsage property for each input column will be as below:
ProductID = 0
Name = 1
OrderYear = 2
OrderQuantity = 3
Notice that you must have at least one input column with PivotUsage 2, one input column with PivotUsage 3, and one input column with PivotUsage 0 OR 1, at least in your pivot transformation.
After setting input columns, go to Pivot Default output, and under the output columns, add these columns: ProductID, Name, 2005, 2006, 2007, 2008 using Add Column button
ProductID and Name columns will show the exact values from the ProductID and Name input columns. So, set the SourceColumn property of the ProductID output column to the LineageID of the ProductID input column.
Select ProductID under the input columns and see the lineageID value there.
From the above screenshot, you can observe that 202 is the LineageID of the input column. So, Set the SourceColumn property of the ProductID output column to LineageID copied from ProductID (i.e., 202)
Do the same for the name column also.
Now its Pivot columns turn, select 2005 under output columns. These columns show the results for the year 2005, so enter 2005 in the PivotKeyValue property.
Data for the output columns 2005, 2006, 2007, and 2008 are coming from the OrderQuantity input column. So, Set the SourceColumn value for 2005, 2006, 2007, and 2008 output columns with the lineageID of the OrderQuantity input column.
In this example, lineageID of OrderQuantity is 211
So, We set SourceColumn with 211 and PivotKeyValue to 2005 for the 2005 output column
For the 2006 output column, We set SourceColumn with 211 and PivotKeyValue to 2006
Do the same for 2007 also.
For the 2008 output column, We set SourceColumn with 211 and PivotKeyValue to 2008
Next, we have to save the output columns in the database. So, let’s configure the OLE DB Destination. To do so, Double click on the OLE DB Destination and provide the required information, such as the Server name, database, and table details of the destination.
Here we selected the Employees database as our destination database and [Pivot table] as our destination table.
Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click OK to finish designing the Pivot Transformation in SSIS 2008 R2 package. Let us run the package
Let’s open the SSMS and check the Pivot Transformation in SSIS 2008 results
