Pivot transformation in SSIS allows you to perform a pivot operation on your input data. In this article, we are going to explain pivot transformation in SSIS 2008R2 using simple example so that you can understand it better. Please refer Pivot Transformation in SSIS 2014 article to understand the changes they made.
Pivot Transformation in SSIS 2008 R2
Scenario: We have the input table as shown below and we want to PIVOT this table using Pivot transformation in SSIS 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 it will open the data flow tab.
Drag and drop OLE DB Source, Pivot transformation and OLE DB destination from 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 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 columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Click ok and edit the Pivot Transformation. 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 input and output properties tab, select pivot default input, under input columns you will find all columns which you selected in previous tab. The only property you must set for each input column is the PivotUsage.
This describe 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 will be 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 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 ProductID and Name input columns. So, set SourceColumn property of ProductID output column to LineageID of ProductID input column.
Simply 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 name column also.
Now its Pivot columns turn, select 2005 under output columns, this columns will 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 theOrderQuantity 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 2006 output column, We set SourceColumn with 211 and PivotKeyValue to 2006
Do the same for 2007 also.
For 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, lets 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 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 SQL Server Management Studio and check the results
Thank you for Visiting Our Blog