Pivot Transformation in SSIS 2008 R2

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 Transformation 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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 0

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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 1

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

PIVOT TRANSFORMATION IN SSIS 2008 R2 2

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)
PIVOT TRANSFORMATION IN SSIS 2008 R2 3

Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

PIVOT TRANSFORMATION IN SSIS 2008 R2 4

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

PIVOT TRANSFORMATION IN SSIS 2008 R2 5

Go to input columns tab and select all input columns that we received from the OLE DB Source

PIVOT TRANSFORMATION IN SSIS 2008 R2 6

Go to the 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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 7

It describes the values you can use in PivotUsage property:

OPTIONSDESCRIPTION
0Column values will pass through the transformation
1Set key. All the columns with the same set key
2This is a Pivot column (These Column values become the Column names)
3Values 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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 8

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

PIVOT TRANSFORMATION IN SSIS 2008 R2 9

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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 10

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)

PIVOT TRANSFORMATION IN SSIS 2008 R2 11

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

PIVOT TRANSFORMATION IN SSIS 2008 R2 12

So, We set SourceColumn with 211 and PivotKeyValue to 2005 for the 2005 output column

PIVOT TRANSFORMATION IN SSIS 2008 R2 13

For the 2006 output column, We set SourceColumn with 211 and PivotKeyValue to 2006

PIVOT TRANSFORMATION IN SSIS 2008 R2 14

Do the same for 2007 also.

For the 2008 output column, We set SourceColumn with 211 and PivotKeyValue to 2008

PIVOT TRANSFORMATION IN SSIS 2008 R2 16

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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 17

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.

PIVOT TRANSFORMATION IN SSIS 2008 R2 18

Click OK to finish designing the Pivot Transformation in SSIS 2008 R2 package. Let us run the package

PIVOT TRANSFORMATION IN SSIS 2008 R2 19

Let’s open the SSMS and check the Pivot Transformation in SSIS 2008 results

PIVOT TRANSFORMATION IN SSIS 2008 R2 20