We have the following excel file that has a pivot table in it:
The first column represents the age (A) while in the columns starting from B to D is the pivoted data. The column names are 10, 11 and 12. In order for SSIS to recognize the numbers as column names you must put a ” ‘ ” in front of the number, like this: ‘10.
Next we open SSIS and create a new project. We add a Data Flow task and in the data flow area we add an Excel source and a unpivot transformation like below:
The red x appears because we have not edited the transformation yet. Let’s do that:
In the Available Input Columns we do not check the columns that we want to remain unchanged. Here we do not want the first column, “Age”, to be changed (unpivoted).
The destination column in the table reprezents the new column where the pivoted values will be stored.
The pivot key column name is the column where the name of the columns will be stored, in our case 10, 11, 12.
After we’ve done all this we can have another excel file or table as a destination for our results.
Cheers!
