CODEWITHSUNDEEP
exceldynamicimportheaderssis
bio_guedes
bio_guedes

Reputation: 33

SSIS dynamic excel column header

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.

Upvotes: 3

Views: 3814

Answers (2)

dodexahedron
dodexahedron

Reputation: 4657

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.

Upvotes: 2

Preet Sangha
Preet Sangha

Reputation: 65476

I had to solve this problem recently. The biggest issue I found is that SSIS with Excel expects a static set of columns.

So I took the set of files and built a similar data flow for each (differing only where the columns were different). I then put a control flow with a File Enumerator to pick up all the files and make the decision based on file name as to the data flow to use.

It wasn't elegant - but it works very well.

Upvotes: 0

Related Questions