0

I feel like this shouldn't be a difficult issue, but I am newer to SSIS and am having trouble finding a solution. I am trying to load two columns of data, but each month I get a new file with an additional column, and a new header. For example, starting in January I receive a file with two columns:

Location Jan-21
Texas 100
Maine 500

Then in February I receive a file like the below:

Location Jan-21 Feb-21
Texas 100 250
Maine 500 450

Each month a new column is added with new data, but I only need to load the newest column's data. I can't figure out how to do this in SSIS, is it possible?. I'm loading from an Excel source to a staging table in a database with two columns (Location & Total).

Improve this question
1
  • Your problem is with this phrase: "each month I get a new file with an additional column [...]". The input data should be structured to add rows over time, not new columns. Perhaps you could only these columns: Location, Date, and <DataValue>? Commented Apr 24, 2021 at 13:27

1 Answer 1

Reset to default
1

SSIS requires a fixed format - that is just your 2 columns or all columns. If the spreadsheet is constantly changing like this - you are not going to be able to import from that spreadsheet.

There are probably ways to setup a separate sheet in Excel that references the specific column you need, but I wouldn't know how to do that.

If looking at just SSIS and I had no other options, I would do the following:

  1. Script Task - get the file to be processed and identify the month to be processed.
  2. Use a condition to redirect to one of 12 different data flows, depending on which file is being processed.
  3. 12 separate file connection - one for each month
  4. Each data flow then selects just the columns needed

Another option would be a pre-processor where you process the file using something like powershell and create a CSV file of just the contents you need.

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

Comments

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.