How to import multiple excel sheets in an excel source to SQL using SSIS?

Question

How to import multiple excel sheets in an excel source to SQL using SSIS?

I will have to import multiple excel files and each file has 8 sheets. Number of sheets on all the input files remains same.

• Each Sheet will be imported to each table.
• Each sheet will have different row # ( Sheet1$A:K, Sheet2$A3:F ..) to start the import process.

How to set this setting in the Excel Source ?

Answer 1

If you are looking to import all sheets in an Excel workbook into SQL you can refer to the following links:

• How to loop over the worksheets in an Excel workbook using Integration Services
• How to read data from multiple Excel worksheets with SQL Server Integration Services

But you must notice that if the excel worksheets have different structures, then you should create multiple excel source, also if loop through many excel files you should create a foreach loop container to loop over files and inside it you should create another foreach loop container to loop over worksheets.