How to read data from multiple Excel worksheets with SSIS

Problem

My Excel source file has data in multiple worksheets and I need to read data from all of these sheets and load it into SQL Server destination table, how can I do this using SSIS?

Solution

This tip explains how we can load data from multiple Excel sheets and load it into a SQL Server destination table.

Data Source

I have created a sample Excel source file and named it Excel_Souce.xls. This sample Excel file has three sheets and all three sheets contain data. The first row of each Excel sheet contains the column names and the data starts in the second row. My sample data looks like the below image.

Destination Table

Let’s create a destination table in which we will load the data after reading from multiple Excel sheets. Please use the below T-SQL code to create the table.

CREATE DATABASE TeachMeSQLServer -- Creating new database
GO
USE [TeachMeSQLServer]
GO
CREATE TABLE [dbo].[ImportMultipleSheets]( -- Creating new destination table
[SHEET] [nvarchar](255) NULL,
[EMP ID] [int] NULL,
[EMP NAME] [nvarchar](255) NULL
)
GO

SSIS Steps to Load Data from Multiple Excel Sheets

Step 1 – Create an SSIS Package

Create a package and name it ReadingMultipleExcelSheets, please refer to the below image.

Step 2 – Create SSIS Variables

Please create a variable as shown below.

• Name = SheetName
• Scope = ReadingMultipleExcelSheets
• Data type = String
• Value = Sheet1$. Sheet1$ (this is the first sheet in the sample Excel source file)

Step 3 – Foreach Loop Container

Add a Foreach Loop Container in the Control Flow Task, please refer to the below image.

Step 4 – Configure the Enumerator to “Foreach ADO.NET Schema Rowset Enumerator”

Edit the Foreach Loop Container and in the Collection section change Enumerator to “Foreach ADO.NET Schema Rowset Enumerator”. Please refer to the below image.

Step 5 – Create a Collection in the Foreach Loop Editor

Create a new connection, in the Collection section. Please refer to the below image.

In the Connection Manager, select “Microsoft Jet 4.0 OLE DB Provider” under provider. Please refer to the below image.

Browse the sample Excel source file, in my case the Excel source file is Excel_Souce.xls. Please refer to the below image.

In the Connection Manager, under the All section update Extended Properties to “Excel 8.0” as shown in the below image.

Test the connection to make sure it is successful as shown below.

Step 6 – Foreach Loop Container Configure Schema

Once the connection to the source file is created, change the Schema value to “Tables” under the Collection section for Foreach Loop Container. Please refer to the below image.

Step 7 – Map Variables in the Foreach Loop Container

In the Variable Mappings section, select the “User::SheetName” variable and make the Index value equal to 2 as shown below.

Step 8 – Add a Data Flow Task inside the Foreach Loop Container

Add a Data Flow Task inside the Foreach Loop Container, please refer to the below image.

Add an Excel source in the Data Flow Task and create a connection to the Excel source file. In my case Excel_Source.xls. Please refer to the below image.

Browse the Excel file and select the First row has column names checkbox if the Excel sheet has the column names in the first row. Please refer to the below image.

Step 9 – Edit the Excel Source

Edit the Excel Source after creating the connection, please refer to the below image.

In the Connection Manager, change Data access mode to “Table name or view name variable” and select the “User::SheetName” variable in the Variable name. Please refer to the below image.

Step 10 – Add OLE DB Destination Task

Add an OLE DB Destination task and configure to load data into the SQL Server destination table. The package should look like the below image after adding the OLE DB Destination task.

Step 11 – Execute the SSIS Package

Execute the package, as you can see from the below image the package executed successfully.

Step 12 – Review the Data

Let’s preview the data in the table, as you can see from the below image data from all three sheets were loaded as per our requirement.

Next Steps

• Check out how to read data from Excel nth row using SSIS.
• Excel Export Rows Limitation is one issue in SSRS 2008R2. You can find the list of other Excel Export Limitations here.
• Check out other tips on SQL Server and Excel integration and working with SSIS and Excel.
• Check out all of the SQL Server Business Intelligence Tips and Tricks.
• Check out my all tips here.