Import Data Using SSIS from an Excel Workbook which has Dynamic Sheets Using SSIS
Importing data from Excel is a simple task if you are
using SSIS. You can accomplish this by simply drag and dropping few controls
and you are done. But what if your excel workbook has dynamic sheets, which
means you will not know the sheet name.
Let us assume we need to import data from excel which has
So we have four sheets, ACC, HR, PROD and IT.
Obviously we need Foreach Loop Container since we need
to import data for each sheet,
Configuring Foreach Loop Container
a string variable to hold the sheet name and assign a value as one of the sheet
names to the variable so that there won’t be initialize errors, also the sheet
name should have suffix $. Eg. HR$
and drop the Foreach Loop Container to the Control Flow and
double click it.
the Collection tab and select Foreach ADO.NET Schema Rowset
Enumerator as the Enumerator as shown below.
New Connection… from the Connection drop down box which will take you to
the Configure ADO.NET Connection Manager screen.
Microsoft Jet 4.0 OLE DB Provider as the provider.
All and enter Excel 8.0 at extended properties.
the Excel file path as the Data Source
Test Connection to verify the connection. Then click OK and once again OK at the
next screen. You will be back at Foreach Loop Editor page.
10. Select Tables for
11. Go to the Variable
mappings section to map the variable by selecting the variable name and the index.
12. That completes the
configuration of the Foreach Loop. This will loop the sheets in ascending
order of sheet names not the sheet order in the excel work book.
Configuring the Data Flow Task
Now we need to configure the Data Flow Task to
use the above dynamic pages.
and drop a Data Flow Task to the foreach loop container.
click the Data Flow Task which will take to the Data Flow task page.
and drop the Excel source to the data flow task.
click the Excel Source and click New to configure the OLEDB connection Manager.
Table name or view name variable for Data access mode and select
the variable used to store sheet name
configure the destination and transformation tasks.
You can verify this by adding a data viewer to the data flow
and download the sample dtsx file at my sky