SSIS : Load and Create table dynamically from excel macro xlsm file

Question

HI Team - I have a vendor who sends excel macro file every Friday. For each month the column header will change. Below is the example of files for each month:

Every time we receive a file there will be always 5 months of data.

December file:

Column1 | Column2| Column3 | December2022 | January2023 | Febrauary2023 | March2023 | April2023

January File :

Column1 | Column2| Column3 | January2023 | Febrauary2023 | March2023 | April2023 | May2023

I want to dynamically load the file through SSIS as the column header is changing every month. I want to load the file into a staging table dynamically.

Once the Staging table is loaded, i want to load the data into final table. The final table structure is below.

Column1 | Column2| Column3 | Month1| Month2| Month3| Month4| Month5

The first column from file after 'column3' should be mapped to 'Month1' in the final table and so on...

Version of Visual studio : SSDT 2017

Could you please help and guide on this. Thank you

Answer 1

HI @kkran ,

SSIS does not have any Dynamic Excel loading component, so you may need to use a .NET script task with like C# to load file, please check out this article:
https://www.codeproject.com/Articles/368530/Dynamic-Excel-file-loading-with-SSIS

A lot of C# code is out of SSIS ability, you may ask C# side for help, you can see this similar thread:

https://learn.microsoft.com/en-us/answers/questions/692361/how-to-create-table-dynamically-in-ssis-using-the.html

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.