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
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
- 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, ScienceINT, 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
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.
