Pivot Transformation in SSIS

The Pivot Transformation in SSIS is used to perform pivot operations on input data (Source Data). A pivot operation in SSIS means converting individual row data into separate columns.

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

Pivot Transformation is precisely opposite to Unpivot Transformation. In our previous article, We already discussed Pivot Transformation in SSIS 2008R2. Though the functionality is the same in SSIS 2008R2, and SSIS 2014, the SQL Server Business Intelligence changed the User interface of the Pivot Transformation in SQL server 2012. This modified version is making the developer’s life much more relaxed compared to 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 example, we are going to perform Pivot Transformation on SQL Server Table to convert row data (such as calendar years 2011, 2012, 2013, and 2014) into separate columns.

Consider below data for this example

Pivot Transformation in SSIS 0

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

Pivot Transformation in SSIS 1

Double click on it, and it will open the data flow tab.

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

Pivot Transformation in SSIS 2

STEP 3: 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.

Pivot Transformation in SSIS 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.

Pivot Transformation in SSIS 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 a bit different when compared to 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 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.

Goto 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

Pivot Transformation in SSIS 7

Stop the debugging mode and Copy the selected values.

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 value region as shown in the below screenshot.

Pivot Transformation in SSIS 8

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

Pivot Transformation in SSIS 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 SQL 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 SQL Server name, database, and table details of the destination.

Pivot Transformation in SSIS 11

Here we selected the [SSIS Tutorials] database as our destination database and [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.

Pivot Transformation in SSIS 12

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

As you can observe from the above screenshot, intelligence is unable to map on its own. So, please map correctly.

Pivot Transformation in SSIS 13

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

Pivot Transformation in SSIS 14

Pivot Transformation Result

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

USE [SSIS Tutorials]
GO

SELECT [Product Name]
     ,[2011]
     ,[2012]
     ,[2013]
     ,[2014]
FROM [PIVOT Transformation in SSIS]
Pivot Transformation in SSIS 15

Comments are closed.