Hi ,

You can do multiple Data flow task with excel connection as source and table as destination to load the data or else you can define the dynamic connection string for the excel,if your excel sheets are consistent with name.

Note :- You can store the connection string in variable or in table and call it through ADO.net connection.

Hope this helps.

Thanks

Hi Praveen,

Based on your description, you want to load multiple sheets from an Excel File into multiple SQL Server tables.

It depends on the metadata (structure) of the excel sheets. If there are no blank sheets and all the sheets with the exact same structure, we can use a for each loop container with a single data flow task. Otherwise you would need separate data flow tasks for each file. Because the metadata for a data flow cannot change during runtime in SSIS and it will be fixed at design time when you map from source to destination component.

References:
http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any
http://www.techbrothersit.com/2015/03/load-multiple-sheets-from-multiple.html

Thanks,
Katherine Xiong

Hi Katherine Xiong,

The metadata (structure) of the excel sheets are different. Is it possible to change excel sheet name and table name dynamically? with one excel source and one destination.

Regards,

Praveen C

Hi Katherine Xiong,

The metadata (structure) of the excel sheets are different. Is it possible to change excel sheet name and table name dynamically? with one excel source and one destination.

Regards,

Praveen C