SSIS Pivot Transformation with UI

Pivoting converts rows into columns.We can perform this task using SSIS Pivot Transformation component.

SQL server 2012 added an UI to Pivot transformation component and makes it simpler. We can configure the pivot operation by setting the options in pivot dialog box.

Sample data: It pulls Total number of items sold by product by month in 2013.

SQL:

SELECT DISTINCT DATENAME([month], SalesDate) AS [Month], productName, SUM(NumberSold) AS Total

FROM     Product

GROUP BY DATENAME([month], SalesDate), productName

ORDER BY productName

Output: 

We want to visualize data as shown below:

Steps:

Start| All Program| SQL Server 2012 |SQL Server Data Tools.

Create a new project – File |New Project |Integration Services Project.

Add “ Data Flow Task ”. Double click the “ Data Flow Task”. Add “ OLEDB Data source”. Double click the “OLEDB data source” and set connection and SQL command as shown below.

Add “Pivot Transform” from toolbox and connect “OLEDB data source” to “Pivot Transform”. Double click the “Pivot Transform” to open the “Pivot UI”. Set the “Pivot Key”, ”Set Key” and “Pivot Value”.

Under “Generate Pivot Output columns from Values”, enter the month name and then click the “Generate Columns Now” button. Click “OK”.

Add “OLEDB Destination” and connect it with “Pivot Transform”. Double click the “OLEDB Destination” and set connection and create a table to output data.

Run the Package.

The package ran successfully. Now open SSMS and query the table where you sent the data.

Output: