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 Enumerator 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 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.
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 drive
]]>