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 SSIS 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 and 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 data flow region
Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL 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 as it is for now
Go to 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, 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 PivotUsage property:
|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 placed in the new columns created by the Pivot Transformation|
So, in this example, 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, one input column with PivotUsage 0 OR 1, at least in your pivot transformation.
After setting input columns, go to Pivot Default output, and the under 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 SourceColumn property of ProductID output column to LineageID of ProductID input column.
Select ProductID under 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 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 PivotKeyValue property.
Data for the output columns 2005, 2006, 2007, and 2008 are coming from the OrderQuantity input column. So, Set SourceColumn value for 2005, 2006, 2007, and 2008 output columns with the lineageID of 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 SQL 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 SQL 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 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