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
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 SSIS toolbox to data flow region
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.
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 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.
The following table illustrates the values you can use in SSIS Pivot Transformation Editor:
|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.
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.
Click on the Generate Columns Now button to generate the pivoted columns. This generated column automatically adds to the Existing Pivoted output Columns 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.
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.
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.
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.
Click ok to finish designing the Pivot Transformation in SSIS 2014 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
USE [SSIS Tutorials] GO SELECT [Product Name] , , , , FROM [PIVOT Transformation in SSIS]