Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          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:   |   Read Comments (1)   |   Related Tips: 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


Last Update:






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





More SQL Server Solutions











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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 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?

 

 


Learn more about SQL Server tools