SSIS Study Note–16 oct 2014 – Read eXCEL FILE WITH MULTIPLE WORKSHEET

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.

image

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…”

image

On Connection Manager, click on “All” and add “Excel 12.0” to Extended Propeties.

image

I have defined a variable named “worksheet” with the scope to the whole package.  I use this variable to store worksheet names.

image

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.

image

If I click on “Set Restrictions” button, I see the columns being returned by the enumerator.

image

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.

image

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.

image

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.

image

Adding IMEX=1 in the connection string helps to do just this.  The string can be accessed from connection manager’s properties.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s