SSIS - dynamic column mappings

Question

I am using SSIS to do data transformation from excel to OLEDB SQL. I have a set of sheets in a folder that i ll have to loop thru and insert the data in each of these sheets to a table. I have a scenario where i have to loop thru a set of Excel sheets that has different column structures. I can loop thru each sheet thru foreach loop enumerator find the filename and pass them on to the Excel source.

I want to know if there is way to escape this column mappings in the destination component which will be a OLEDB SQL table in my case. Because these mappings are different for each file. Is there way to do this dynamically?

Answer 1

While you can add a script task inside the loop to modify the mappings, it is not the easiest thing to do as you have to explicitly create each mapping in code. A simpler solution would be to replace excel sheets with delimited text files and import them inside the loop using the Bulk Insert Task. You will not have to provide any mapping info as long as the column order is the same in both files and tables.

If you can't do that, you will hove to store the mapping metadata somewhere, e.g. a table in your database and use this to create the dynamic mappings in a Script task.

A slightly easier way is to use just one script task that uses the metadata to configure an SqlBulkCopy object. You lose the flexibility of a full Data Flow task but if all you want is to load some files in temp talbes, it is good enough. Plus, it's a whole lot easier to configure a SqlBulkCopy object than a Data Flow task.