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


By:   |   Updated: 2016-02-03   |   Comments (7)   |   Related: More > Integration Services Excel


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.

Excel_Data_Multiple_Sheets

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.

Create_Package

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)
Variable_Properties

Step 3 - Foreach Loop Container

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

Add_For_Each_Loop_Container

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.

Edit_For_Each_Loop_Container

Step 5 - Create a Collection in the Foreach Loop Editor

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

Create_Connection_For_Each_Loop_Container

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

Select_Connection_Provider

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

Browse_Excel_Source

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

Modify_Connection_Properties

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

Test_Connection

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.

For_Each_Loop_Conatainer_Configuration

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.

For_Each_Loop_Container_Variable_Mapping

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_Data_Flow_Task

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.

Configure_Excel_Source_In_DFT

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.

Browse_Excel_Source_File

Step 9 - Edit the Excel Source

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

Add_Excel_Source_Task

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.

Excel_Source_Data_Access_Mode

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.

Data_Flow_Task

Step 11 - Execute the SSIS Package

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

Execute_Package

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.

Data_Preview
Next Steps





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


Article Last Updated: 2016-02-03

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


Credit:
http://lolaru.blogspot.com/2017/07/error-getenumerator-method-of-foreach.html

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)

Hello,

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)

 Ghanesh,

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?

 

 



download














get free sql tips
agree to terms