Pivot Transformation in SSIS

The Pivot Transformation in SSIS performs pivot operations on input data (Source Data). A Pivot Transformation in SSIS means converting individual row data into separate columns.

We use the SSIS Pivot transformation to present the data in a crosstab or tabular format to understand it better. It will perform row based operations and rotate rows to columns and the corresponding value placed under those columns. If there is no value, it will fill with NULLs.

Here, we will explain Pivot Transformation in SSIS using a simple example so that you can understand it better.

Pivot Transformation is precisely the opposite of Unpivot Transformation. In our previous article, We already discussed Pivot Transformation in SSIS 2008R2. Though the functionality is the same in SSIS 2008R2 and 2014, the Business Intelligence changed the User interface of the Pivot Transformation in SQL server 2012. This modified version makes the developer’s life much more relaxed than the earlier version. Please refer to Pivot Transformation in the 2008R2 article to understand the difference between Pivot Transformation in 2014 and 2008R2.

NOTE: It’s not mandatory, but it is always best practice to use Sort Transformation before Pivot Transformation for the unsorted data. Because sometimes, SSIS Pivot Transformation gives strange results for unsorted data.

Pivot Transformation in SSIS Example

In this SSIS example, we will perform Pivot Transformation on the table below to convert row data (such as calendar years 2011, 2012, 2013, and 2014) into separate columns.

Consider the below data for this example.

Source table

STEP 1: Open BIDS, drop the data flow task from the toolbox to control flow, and rename it PIVOT Transformation in SSIS 2014.

Data Flow Task 1

Double click on it, and it will open the data flow tab. For more Transformations >> Click Here.

STEP 2: Drag and drop OLE DB Source, Pivot Transformation from the toolbox to the data flow region

Pivot Transformation in SSIS 2

STEP 3: Double-clicking the OLE DB source in the data flow region will open the connection manager settings and provide space to write our statement.

OLE DB Source Editor 3

SQL Command we used for SSIS Pivot Transformation 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)

STEP 4: Click on the columns tab to verify the columns. Here, we can uncheck the unwanted columns also.

View Available Input Columns 4

Click ok and drag the output arrow of the OLE DB Source to the Pivot Transformation to perform pivot transformation on the source data.

Configure SSIS Pivot Transformation

STEP 5: Double Click or edit the Pivot Transformation. It will open the Pivot window, which is slightly different from the Pivot Transformation in SSIS 2008R2.

Pivot Transformation in SSIS 5

The following table illustrates the values you can use in SSIS Pivot Transformation Editor:

OPTIONSDESCRIPTION
0Column values will pass through the transformation
1Set key. All the columns with the same set key
2It’s a Pivot Key (These Column values become the Column names)
3It’s a pivot value, and values from this column will be placed in the new columns created by the Pivot Transformation.

So, in this SSIS Pivot Transformation example:

ProductID = 0

Name = 1 (Set Key)

OrderYear = 2 (Pivot Key)

OrderQuantity = 3 (Pivot Value)

Pivot Transformation in SSIS 6

Click ok and run the SSIS Pivot Transformation package. Go to the progress tab and check the column names as shown below.

NOTE: For 3 or 4 columns like this example, we can directly replace the [Value1], [Value2], and so on with the column names, but in real-time, we may deal with 50 years then it is impossible to write those 50 years in the Generate pivot output columns from value region. So, it is always good practice to run the package once and then copy the column names from the progress Tab.

Progree Tab 7

Stop the debugging mode and Copy the selected values.

Generate Columns

Again, double-click on the SSIS Pivot Transformation in the data flow region and paste the column names in the Generate pivot output columns from the value region, as shown in the screenshot below.

Click the Generate Columns Now button 8

Click on the Generate Columns Now button to generate the pivoted columns. This generated column automatically adds to the Existing Pivoted output Column’s region.

Generate Output Column Result 9

Click ok on the Generate Output Column Results window to generate the output columns with names as shown in the above screenshot.

Pivot Transformation in SSIS 10

From the above screenshot, if you can observe the Existing Pivoted output Columns region, output columns (C_2011_OrderQuantity, C_2012_OrderQuantity, C_2013_OrderQuantity, and C_2014_OrderQuantity) are generated for the source data.

Click OK.

Now, we have to preserve the output columns in the database. So, Drag and drop the OLE DB Destination from the toolbox to the data flow region. Next, Drag the SSIS Pivot Transformation output arrow into the OLE DB Destination.

Double-click on the OLE DB Destination and provide the required information, such as the SQL Server name, database, and table details of the destination.

Select Destination Table 11

Here, we selected the database as our destination database and the [Pivot Transformation in SSIS] table as our destination table.

Click on the Mappings tab to check the Pivot Transformation source columns mapped to the destination columns.

Mapping available Input and Destination Columns

NOTE: If your input & destination column names are the same, the intelligence automatically maps. If there are any changes in the column names (alias or calculated columns), we must map them manually.

As you can observe from the above screenshot, intelligence cannot map independently. So, please map correctly.

Pivot Transformation in SSIS 13

Click ok to finish designing the SSIS Pivot Transformation package. Let us run the package.

Pivot Transformation in SSIS 14

Pivot Result

Let’s open the SSMS and write the below query to check the SSIS Pivot Transformation results.

SELECT [Product Name]
     ,[2011]
     ,[2012]
     ,[2013]
     ,[2014]
FROM [PIVOT Transformation]
Destination Table 15

Pivot Transformation in SSIS 2008 R2

Pivot transformation in SSIS allows you to perform a pivot operation on your input data. Here, we will explain pivot transformation in SSIS 2008R2 using a simple example so that you can understand it better.

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 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 the data flow region.

PIVOT TRANSFORMATION IN SSIS 2008 R2 2

Double click on the OLE DB source in the data flow region will open the connection manager settings and provide 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)
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 it as it is for now.

PIVOT TRANSFORMATION IN SSIS 2008 R2 5

Go to the 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 SSIS Pivot Transformation input and output properties tab, and select pivot default input. Under input columns, you will find all the columns that 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 the 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 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 follows:

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

PIVOT TRANSFORMATION IN SSIS 2008 R2 8

After setting input columns, go to Pivot Default output, and under the output columns, add these columns: ProductID, Name, 2005, 2006, 2007, 2008 using the 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 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.

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 the 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 the PivotKeyValue property.

Data for the output columns 2005, 2006, 2007, and 2008 come from the OrderQuantity input column. So, Set the SourceColumn value for the 2005, 2006, 2007, and 2008 output columns with the lineageID of the 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

Destination Table

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.

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 are 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

Comments are closed.