The Pivot Transformation in SSIS is used to perform pivot operations on input data (Source Data). A pivot operation means converting individual row data into separate columns. Pivot Transformation is exactly opposite to Unpivot Transformation
In our previous article, We already discussed Pivot Transformation in SSIS 2008R2. Though the functionality is 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 changed version is making developers life much easier, compared to earlier version. Please refer Pivot Transformation in 2008R2 article to understand the difference between Pivot Transformation in 2014 and 2008R2.
In this article, we are going to explain Pivot Transformation in SSIS 2014 using simple example so that you can understand it better.
NOTE: It is not mandatory but it is always best practice to use Sort Transformation in SSIS before Pivot Transformation if the data is not sorted because, sometimes 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 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 in the above screenshot is same 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 columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Click ok and drag the output arrow of OLE DB source to the Pivot Transformation to perform pivot transformation on the source data.
STEP 5: Double Click or edit the Pivot Transformation. It will open the Pivot window which is bit different when we compared to Pivot Transformation in SSIS 2008R2.
This describe the values you can use in Pivot Transformation Editor in SSIS:
|0||Column values will pass through the transformation|
|1||Set key. All the columns with the same set key|
|2||This is a Pivot Key (These Column values become the Column names)|
|3||This is 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 package.
Goto the progress tab and check the column names as shown in the below screenshot.
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 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. These generated column will automatically added 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 save the output columns in the SQL database so, Drag and drop the OLE DB Destination from toolbox to data flow region and then, Drag the 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 Mappings tab to check whether the source columns are exactly mapped to the destination columns.
NOTE: If your input column names, and the destination column names are same, the intelligence will automatically map. If there is any changes in the column names (any alias columns or any calculated columns), we have to manually map them.
As you can observe from the above screenshot, intelligence is unable to map on its own. So, please map them correctly.
Click ok to finish designing the Pivot Transformation in SSIS 2014 package. Let us run the package
Let’s open the SQL Server Management Studio and write the below SQL statement to check the results
USE [SSIS Tutorials] GO SELECT [Product Name] , , , , FROM [PIVOT Transformation in SSIS]
Thank you for Visiting Our Blog