SSIS dynamic excel column header

Question

I am having a problem using SSIS and hoped someone could help me. I am new to this Microsoft software and I was asked to import data from a Excel schedule file that features 13 weeks. So the data is displayed as followed in the Excel file:

Person id | Person age| School activity | Activity leader| 18-06-2012 | 25-06-2012 | ..(more weeks)

1452      | 14        | Painting        | John Smith     | 2          | 6          | ...

where 2 and 6 represent the amount of hours spent on the this activity per week.

Although, person id, person age, school activity and activity leader are static column header in this excel file, the weeks column header while change from file to file.

Therefore, I do not know how to import such a data file to my database because I am having mapping issues from a file to another.

I want the data placed into the database as separate rows for each of the week columns, so I am using the unpivot transform to achieve that. What I want is to be able to import the data regardless of the column header.

Answer 1

If the data can be provided as CSV, you can use a Flat File Source and then skip the header row, so long as there will always be the same number of columns with the same, predictable formatting.

You mentioned in the discussion that you would be using the unpivot transform to turn the data into individual rows, so you know how to do the rest.