SSIS - How To Use Pivot Transformation in SSIS 2012

Scenario: 
We have a table with four columns (SalePerson, SaleYear, SaleMonth, SaleAmount). We want to pivot this data as shown below by using SSIS.

Source Table definition and sample data

CREATE TABLE dbo.Sale
  (
     PersonName VARCHAR(100),
     SaleYear   INT,
     SaleMonth  VARCHAR(15),
     SaleAmount INT
  )

go

insert into dbo.Sale
Select 'Aamir',2012,'January',100
Union All
Select 'Aamir',2012,'February',200
Union All
Select 'Aamir',2012,'March',900
Union All
Select 'Aamir',2012,'April',456
Union All
Select 'Aamir',2012,'May',123
Union All
Select 'Aamir',2012,'June',332
Union All
Select 'Aamir',2012,'July',543
Union All
Select 'Aamir',2012,'August',765
Union All
Select 'Aamir',2012,'September',900
Union All
Select 'Aamir',2012,'October',100
Union All
Select 'Aamir',2012,'November',500
Union All
Select 'Aamir',2012,'December',100
Union All
Select 'Aamir',2013,'January',20
Union All
Select 'Raza',2011,'February',1001

Solution : 

To perform pivot operation on given source data we will be using Pivot Transformation in SSIS.

Step 1: 
In newly created SSIS Package, bring the data flow task to the Control Flow Pane. Inside Data Flow task , bring OLE DB Source and choose dbo.Sale table as shown below as source.

Step 2: 
Drag Pivot Transformation from the SSIS ToolBox to Data Flow Pane. Connect OLE DB Source to Pivot Transformation as and then configure as shown below

Pivot Key : Provide the column name which value you want to convert to columns
Set Key : Provide the column which values will be grouped as a row value
Pivot Value: Provide the column which has values 

Note : You can ignore to provide the values and run your package in debug and then copy from output window or progress window and reopen and provide in Generate pivot output Column from values text box. As I know the values for my column those are month names , I have provided by myself or I could have run the package with Ignore and copy them later.

Step 3: 
Generate Columns as shown below 

Hit Generate Columns Now button


Pass Through Columns : 
According to our requirement we also need SalePerson column but in above configuration we could not find a ways to add that column. To add passing through columns we have to go to Advance Editor. Right click on Pivot Transformation and Click on Shown Advance Editor and configure step by step as done below

Step 4:
Select the passing through column on Input Column Tab. 


Step 5:
Click on Input and Output Properties Tab and then note down the LineageID of SalePerson Column that is 17 in our case.


Step 6: 
Add new column with name SalePerson under Pivot Default Output as shown 

Step 7:
Rename all the column as you want. I removed the C_ and SaleAmount from each of the column as shown below

Final Output :
Just to show the output , I have used the data Viewer between Pivot and Multicast. Multicast is only used as test destination. In real scenario you will be inserting these records in some table or writing to output file etc.
As we can see that the Pivot transformation is working according to our expectation and converted our source data to our required format.



2 comments: