Pivot Transformation in SSIS

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

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 example, we are going to perform Pivot Transformation on the below Table to convert row data (such as calendar years 2011, 2012, 2013, and 2014) into separate columns.

Consider the below data for this example.

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

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

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

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.

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.

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 Pivot

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.

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

OPTIONS	DESCRIPTION
0	Column values will pass through the transformation
1	Set key. All the columns with the same set key
2	It’s a Pivot Key (These Column values become the Column names)
3	It’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)

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

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 below screenshot.

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

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

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

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 (any alias or calculated columns), we have to map them manually.

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

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

Pivot Transformation 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]