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.

Excel Sheet

Let us assume we need to import data from excel which has
four sheets.

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

1. Create
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$

2. Drag
and drop the Foreach Loop Container to the Control Flow and
double click it.

3. Select
the Collection tab and select Foreach ADO.NET Schema Rowset
as the Enumerator as shown below.

4. Select
New Connection… from the Connection drop down box which will take you to
the Configure ADO.NET Connection Manager screen.

5. Click
New… button.

6. Select
Microsoft Jet 4.0 OLE DB Provider as the provider.

7. Select
All and enter Excel 8.0 at extended properties.

8. Enter
the Excel file path as the Data Source

9. Click
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
the schema.

11. Go to the Variable
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.

1. Drag
and drop a Data Flow Task to the foreach loop container.

2. Double
click the Data Flow Task which will take to the Data Flow task page.

3. Drag
and drop the Excel source to the data flow task.

4. Double
click the Excel Source and click New to configure the OLEDB connection Manager.

5. Select
Table name or view name variable for Data access mode and select
the variable used to store sheet name

6. Then
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


One Response to “Import Data Using SSIS from an Excel Workbook which has Dynamic Sheets Using SSIS”

  1. Thanks!

    Very important point – 1.”…assign a value as one of the sheet names to the variable so that there won’t be initialize errors…”

    I spent a whole day with otherwise correct process, which did not work, because I left sheet name variable empty.


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |