2

I have an Excel sheet that changes column names based on the year and current week of the year, so for example 201901 would be the first week of 2019.

The Excel sheet that is sent to us daily automatically adjusts the column names based on the current date (up to 6 months), so currently (31/07/2019) the year and week show 201931 - 202011:

enter image description here

So the N column next week will be 201932 (the columns shift left basically).

I have tried changing the Excel source columns to a different alias of just 1,2,3,4 etc in hopes to just get the data into SQL Server, and then script a trigger in SQL Server to change column names but doesn't work due to the mapping SSIS requires.

enter image description here

Works fine until the column changes to next week.

A simple method would be to drop the table and just dump the file in a new table named the same but can't see how to set up in SSIS as you need to map the column names (which unfortunately change).

Here is how the dataflow looks:

enter image description here

Ideally, for me, something like this would be perfect:

enter image description here

But not sure how to achieve this outcome in SSIS?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DarrenB
  • 75
  • 6
  • 1
    Check out the following post, it may help you https://stackoverflow.com/questions/54781017/how-to-map-input-and-output-columns-dynamically-in-ssis – Yahfoufi Jul 31 '19 at 11:37

1 Answers1

0

I would suggest transforming the data. Currently, you have a "cross-table"-format.

How about putting the Excel data in the form of (RAG_week; CalenderWeek; Value_of_CalenderWeek) ? For doing this you can use an Excel-Macro which fills a new Sheet in the Excel file. (Each cell is transformed in one dataset, being a row on its own.) Next, you create a similar table on the SQL Server. Then you can create a SSIS package with constant column assignment, simply appending the new data each week.

This impacts the further evaluation of your data, but seems to be a far more stable approach.

Nick Oetjen
  • 129
  • 4
  • The issue with altering the columns in Excel is that I don't actually have access to the original file. We automatically receive the file within our directory once a day which replaces the current file we have. I would have thought SSIS would have a simple way to just dump the file as-is into a new table without having to map the columns. – DarrenB Jul 31 '19 at 11:28