Best Way to ETL Multiple Different Excel Sheets Into SQL Server 2008 Using SSIS

0
votes

I've seen plenty of examples of how to enumerate through a collection of Excel workbooks or sheets using the Foreach Loop Container, with the assumption that the data structure of all of the source files are identical and the data is going to a single destination table.

What would be the best way to handle the following scenario: - A single Excel workbook with 10 - 20 sheets OR 10 - 20 Excel workbooks with 1 Sheet. - Each workbook/ sheet has a different schema - There is a 1:1 matching destination tables for each source sheet. - Standard cleanup: workbooks would be created and placed in a "loading" folder, SSIS package runs on a job that reads the files in the loading folder and moves them to an archive folder upon successful completion

I know that I can create a seperate SSIS package for each workbook, but that seems really painful to maintain. Any help is greatly appreciated!

1
sqlssisetl
As each sheet has a different schema, there is no easy way to implement this. It is not possible to dynamically set the sheet for an Excel workbook using the standard SSIS connection managers. I would create a data flow for each sheet. - Jeroen Bolle
I agree. There is no way to autoamatically map columns. Even if you use same column names. Only some coding could help you a bit, but that's a lot of work. - devarc

1 Answers

1
votes

We faced the same issue long back. I will just summarize you what we have done. We have written an SSIS-package pro-grammatically using C#. A MetaTable is maintained which holds the information (table name, columns, positions of these columns in the flat file.) of the flat files. We extract the flat file name and then query it to the meta-table about the table this flat file belongs to and the columns it is having and the column positions in the flat file.

We execute the package in the SQLSERVER by passing each flat file as a command line argument to the PackageExe. So it reads and processes each flat file.

Example Suppose say we have a flat file FF, we first extract name of the flat file and then get the table name by querying to the DB, lets say it is TT which contains columns COL-1, COL-2 with positions 1 to 10 and 11 to 20 respectively. By reading this information from the MetaTable now I have created a derived column transformation (package.)

Our Application has a set of flat files in a folder and by using "For Loop Container SSIS" we get one flat file at a time and do the above process.