Pivot Transformation in SSIS

- It is being used to transpose rows into columns just like Excel Transpose
- In T-SQL also, we have PIVOT command available which is more reliable and faster than SSIS
- Pivot in SSIS is not recommended generally


This is our original dataset

And this is our desired result

EXAMPLE


Pre-requisite


Following script has to be executed

CREATE TABLE PivotDemo (Student VARCHAR(10), Subject VARCHAR(10), Mark INT)

CREATE TABLE PivotOutputDemo (Student VARCHAR(10), Mathematics INT, Science INT, EnglishLanguage INT)

INSERT INTO PivotDemo VALUES

('Nisarg','Maths',100),

('Nisarg','Science',100),

('Nisarg','English',99),

('Megha','Maths', 98),

('Megha','Science', 100),

('Megha','English', 95),

('Swara','Maths', 96),

('Swara','Science', 80),

('Swara','English', 90)

Steps


1. Add Data Flow Task in Control Flow
2. Drag source in DFT and it should point to PivotDemo table
3. Drag Pivot transformation and connect it with source
4. Go to "Input columns" tab and select all 3 columns

5. Go to "Input & output properties" tab and expand "Input columns". Set PivotUsage property as below.


Student --> 1
Subject --> 2
Mark --> 3

Definition of PivotUsage value

• 0 – the column is passed through unaffected
• 1 – the column values become the rows of the pivot (aka the Set Key)
• 2 – the column values become the column names of the pivot (aka the Pivot Column)
  
• 3 - the column values that are pivoted in the pivot

6. Expand Pivot Default output and add following columns with following properties.

7. Name tells that what is the output name you want to have

8. PivotKeyValue tells that what is the value existing in original source for which you want to generate a column.

9. SourceColumn signifies LineageID property. For Student it should be same as LineageID property of "Student" Input column. But for remaining 3 columns, it would be LineageID property of Input column which has PivotUsage value as 3 i.e. Marks. (highlighted in 4th figure)

10. Drag destination and connect it with Pivot and do proper mapping.

11. Execute the package and desired result would be there in output table.

PROBLEMS

- If any new subjects gets added in source table, then SSIS package has to be updated to add corresponding output column

- If we want to make it dynamic then we need to use script task

- configuration of Pivot transformation is bit tricky and erroneous, so generally it should be avoided.