Unpivot Transformation

Using this transformation we can convert the data from normalized format to denormalized format i.e. we can convert the columns into rows.

Source file:

Id	Name	Jan	Feb	Mar	Apr	may
100	ravi	10000	12000	12000	15000	15000
101	rajesh	15000	17000	17000	20000	20000
102	raj	15000	15000	18000	18000	20000

Targets file Structure:

                      Id                 integer

                      Name           varchar

                      Salary           integer

                      Month name   varchar

Step1: add Data flow task to control flow—click on edit

Step2: Create connection for flat file and target database

Step3: drag flat file source and configure the flat file source.

          Convert the data types if required at source level.

Step4: drag Unpivot transformation –right click on Unpivot and click on edit

           Select the column check boxes which we are going to convert into records.

           And give the destination column name as Salary, Pivot key value name is   

           Month name

If u execute the the output is:

Id	Name	Salary	Month name
100	ravi	10000	Jan
100	ravi	12000	Feb
100	ravi	12000	Mar
100	ravi	15000	Apr
100	ravi	15000	may
101	rajesh	15000	Jan
101	rajesh	17000	Feb
101	rajesh	17000	Mar
101	rajesh	20000	Apr
101	rajesh	20000	Jan
102	raj	15000	Jan
102	raj	15000	Feb
102	raj	18000	Mar
102	raj	18000	Apr
102	raj	20000	may