CODEWITHSUNDEEP
sql-serverexcelssisload
Lucas
Lucas

Reputation: 23

SSIS Load Excel file with dynamic columns

I'm new to SSIS and have been looking for a solution to this challenge for a week without success.

Actually I receive an Excel file that has around 200 columns, but, to keep it simple, let's imagine that I have 10 columns, the first 3 columns are Product Name, Product Category and Client Name, then all other columns are the Months with the sales forecast, with it, I have something like:

Product ! Category ! Client ! Jan-18 | Feb18 | Mar18 !
Prd A   | Cat A    | ClientA| 10     | 20    | 25    | 
Prd B   | Cat B    | ClientB| 70     | 30    | 100   |

My problem is that every month I'll get a new month in this file , and I cant' import only the last month, as the historical data can change along the time .

So, next month I can have , for instance: 

Product ! Category ! Client ! Jan-18 | Feb18 | Mar18 ! Apr18|
Prd A   | Cat A    | ClientA| 50     | 40    | 250   | 40   |
Prd B   | Cat B    | ClientB| 50     | 40    | 150   | 80   |

How can I import this into SQL? I was thinking of doing it using Script Task but was not able to write the script as I don't know C# or VB.

Is there a way to perform this load without Script Task? If so how? If just possible with ST, can you help me writing this?

Upvotes: 1

Views: 4243

Answers (1)

Jader Gabriel Soares de Arruda
Jader Gabriel Soares de Arruda

Reputation: 56

Lucas, one workaround that I use when I face this same problem is, import the table to a staging table with only one varchar(max) column. Don't forget to include a delimiter. This way, SSIS will accept the file with as many column as it has. Once this data is inside your database, you can create a procedure to break this data into multiple columns and send to the definitive table. I know there are other ways but this process has worked for me.

Upvotes: 1

Related Questions