Use SSIS to import all of the worksheets from an Excel file

This blog shows how to loop over the worksheets in an Excel workbook, importing the contents of each.  The technique will only work if the worksheets are all in the same format.

Our example

We want to consolidate three separate months of shopping data into a single SQL Server table:

Each month contains a separate list of shopping trips, but (crucially) they share the same format.

Here's what the end result should be:

What the final answer should look like (although the rows won't necessarily be in this order).

If you want to work through the case study in this blog, first run this script in Management Studio to create the table you'll need:

That's enough about what we need to do.  Time now to do it!

The underlying principle

We need to get a list of all of the names of the worksheets in our workbook:

We'll never see this, but this is the collection of names that we need to loop over.

Step 1 - Create a variable to hold each worksheet's name

The first thing to do is to create a variable to hold each worksheet's name in turn:

It's crucial that you give a value to this variable equal to the name of one of the worksheets that you want to import, otherwise you won't be able to get the data flow task working later.

Step 2 - Add the basic package tasks

Create a package which deletes any old rows from the AdoShopping table, then has an empty loop and an empty data flow task:

The Execute SQL task will run the command TRUNCATE TABLE AdoShopping (if you don't know how to set this up, you're probably reading the wrong blog).  See below for how to configure the other tasks.

Step 3 - Creating a connection for the Foreach Loop

Double-click on the Foreach Loop task to edit it, and say what sort of loop it is:

On the Collection tab, specify that this loop should be an ADO.NET Schema Rowset Enumerator (!).

Now choose to create a new connection:

Choose to create a new connection

Ask to create a new connection:

Click to create a new connection.

Choose the provider that you want to use:

It may seem strange that you use an Access provider to connect to an Excel worksheet, but ...

Now type in the Excel workbook to which you want to link:

Make sure that you include the full file path and file name (obviously your path will be different to the one shown here).

On the All tab, set the Extended Properties to Excel 12.0;HDR-YES;:

Type this in carefully!

Test your connection:

Click on this button at the bottom left to test your connection.

You should get this message:

Yeah!  Or if you get an error, check you haven't left the workbook open in Excel.

Select OK until you exit the dialog box for configuring your connection, then optionally rename the connection:

Rename the connection that you've created for your package.

Step 4 - Configuring the rest of the Foreach Loop

Double-click on your Foreach Loop to edit it, and specify that you want to return a list of the Excel tables:

Tables may seem an odd choice, but this is internally how SQL will get at the worksheet names.

On the Variable Mappings tab, choose to store the second bit of information returned in your string variable:

Type in number 2 as the index number (for why, see hint below).

Step 5 - Configuring the data flow task

Here's what the data flow task will look like:

The details of how to export to SQL Server aren't shown here.  The Excel source should point to the Shopping.xlsx workbook.

For the Excel source, choose as the data access mode that the worksheet name is contained in a table variable, and specify which:

Each time round the loop, the Excel source will point to the worksheet whose name will be contained in the SheetName variable.

Here are the mappings for the SQL Server destination:

The mappings from Excel source to SQL Server destination table AdoShopping.

 Step 6 - running the package

The final thing to do is to run the package:

What you should see in control flow

The data flow task will flash once for each worksheet:

At the end of the package, this is what the data flow should show (since there are 6 rows in the March worksheet, the last one to be imported).

And that's it!  Told you it was a messy procedure ...