3

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.

Improve this question
9
  • Are there always the same number of columns or are there different numbers of columns per file? Commented Jun 18, 2012 at 12:03
  • Yes, there are. It is just the week columns that change. This week I have 13 weeks starting from 18-06-2012 going up to 10-09-2012. So next week I'll have another one with the same columns but the week columns will be from 25-06-2012 up to 17-09-2012. Commented Jun 18, 2012 at 12:16
  • Is it possible for the data to be dumped as plain csv files? The flat file importer doesn't really care what the column headers are, so if you can bring in a consistently-formatted file (13 columns, comma-separated, quoted strings, for example), you can simply skip the header row and then perform whatever manipulation you want on the data itself. Commented Jun 18, 2012 at 12:23
  • Also, can you define the schema mapping for us? In other words, you have these columns in Excel... What are the corresponding columns in your database/how is the data represented in the database? Commented Jun 18, 2012 at 12:25
  • The columns in the database are the following: Person id->Person id Person age->Person age School activity->School activity Activity leader->Activity leader 18-06-2012,25-06-2012,... -> Week Rows below the week label -> Amount of hours I am planning on getting those columns with the unpivot transformation. Commented Jun 18, 2012 at 12:57

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

1 Comment

Well, the issue is that doing so, I would have to create a data flow each time I had a new schedule file. Isn't there a way to this automatically?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.