Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

By:   |   Last Updated: 2016-02-03   |   Comments (3)   |   Related Tips: 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

Last Updated: 2016-02-03

get scripts

next tip button

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.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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


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

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


Tuesday, May 09, 2017 - 1:38:48 PM - eric81 Back To Top


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?



Learn more about SQL Server tools