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:
