Scenario

Every month a company receive customers data from an excel file to extract and load data into company data warehouse. In brief, is required to extract data from a single excel file with multiple worksheets with same schema into a single destination table in SQL server 2005. Example of an excel file with three worksheets

Purposed solution

The purposed solution is creating a SQL Server Integration Services (SSIS) package that in two steps:

1. Get a list of all the worksheet names inside excel file (assuming that is unknown before running the package)

2. Use the foreach loop task to pick each worksheet name that will be dynamically read by excel source component and loaded into destination component (Ex: SQL Server, Oracle…)

Read the entire StepByStep solution in the attached document below.