Things to note:
I am using a For-each Loop container and a Data flow Task inside it to read an Excel file with multiple worksheets and write the result to a flat file destination.
For the loop container, the enumerator is “Foreach ADO.NET Schema rowset”..
For the connection, we are using “.Net providers for OleDb\Microsoft access 12.0…”
On Connection Manager, click on “All” and add “Excel 12.0” to Extended Propeties.
I have defined a variable named “worksheet” with the scope to the whole package. I use this variable to store worksheet names.
Back to Loop container. I select the newly created connection, and for “Schema” I select “Tables”. Here, each worksheet from the Excel file is considered as a table.
If I click on “Set Restrictions” button, I see the columns being returned by the enumerator.
I use “Table_Name” as this column will contain the worksheet name. This is the 3rd (0 starting index) column of the result-set. So, in “Variable Mappings”, I select the “worksheet” variable and assign index 2 to it.
Now, back to my Data flow Task, I have an excel source and a flat file destination. for the Excel source, I select “Table name or view name variable” for data access mode and select the “worksheet” variable.
This means the Excel source will get the name of the worksheet to read from the user::worksheet variable. The User::worksheet variable is getting populated from the for-each loop container with the names of all worksheets from the source excel file.
Some other tips:
Instead of for-each loop we can use a single Excel source and inside that we can write SELECT-UNION statement with the names of the worksheet. this is easier to do if we know the names and number of worksheets beforehand. if we do not know about the names and number of worksheets then for-each method is better.
When reading from Excel source, SSIS uses the first 8 columns to decide the data type for the columns. This can create lot of problems. To stop SSIS from guessing data type from 8 columns the easiest and best approach is to tweak the connection string of the connection manager.
Adding IMEX=1 in the connection string helps to do just this. The string can be accessed from connection manager’s properties.