How to read data from multiple Excel worksheets with SQL Server Integration Services

By:   |   Comments (7)   |   Related: More > Integration Services Excel


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?


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
USE [TeachMeSQLServer]
CREATE TABLE [dbo].[ImportMultipleSheets]( -- Creating new destination table
	[SHEET] [nvarchar](255) NULL,
	[EMP ID] [int] NULL,
	[EMP NAME] [nvarchar](255) NULL

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, July 29, 2021 - 9:20:09 AM - Itai Seri Back To Top (89073)
I've gotten the following error:
Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate.

I was trying to import xlsx file.
To fix this:
Change provider to :Net Providers for OleDB\Microsoft Office 12.0 Access Database Engine.

Change the Extended Properties:
since we are going to use an excel file

Click on Data Links -> Data Links Properties-> Change Extended Properties to EXCEL 12.0


Tuesday, June 8, 2021 - 12:04:39 PM - Alexander Almanzar Back To Top (88823)
I'm also having the same issue with vs 2019. Adding a pre value to the SheetName variable gets rid of the validation issues, but ones the iteration kicks off this happens "[Excel Source [2]] Error: Opening a rowset for "'Events$'_" failed. Check that the object exists in the database."

Tuesday, May 18, 2021 - 10:03:24 AM - Murali Back To Top (88699)
Its working for me, but I cant able to delete the file after processing. Getting Error Message: Used by another process".
Can you please suggest how to delete.

Tuesday, August 11, 2020 - 5:39:52 AM - prasad Back To Top (86273)
why you mention index = 2 while mapping the variable in foreach loop container

Friday, May 24, 2019 - 7:00:15 AM - Web Back To Top (80188)


I have done exactly the same thing using VS studio 2019 but it's throwing an error.

In fact, the variable is not getting populated, it's value is always Sheet1$

Wednesday, January 17, 2018 - 6:28:13 PM - Grace O Back To Top (74980)

 Thank you soo much for this! Helped me with my project today :)


Tuesday, May 9, 2017 - 1:38:48 PM - eric81 Back To Top (55624)


Grear article, but one thing i'm having issue with is when I define Foreach ADO.NET Schema Rowset Enumerator none of the fields show up in my display.  Any ideas why?



get free sql tips
agree to terms