Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (13)   |   Related Tips: More > Integration Services Excel

Problem

I have data in multiple Excel files and all my Excel files are placed in the same folder. I want to create a SQL Server Integration Services (SSIS) Package which can read data from multiple Excel files and load the data into a SQL Server destination table. How can I achieve this using SSIS?

Solution

This tip explains how we can read data from multiple Excel files using SSIS and load the data into a SQL Server destination table. Please follow all the steps below to understand the solution.

Step 1 - Folder Creation

Please create a folder named Excel_Exercise on the C:\ Drive, and then please create a child folder named Excel_Source. Let's create three excel files inside the Excel_Source folder. Please refer to the image below as a reference.

Excel Source Folder

We will add data these three Excel files and then we will read data from all these Excel files using SSIS.

Step 2 - Sample Files

I have already created three empty Excel source files in my previous step. Let's add data into sheet1 of each Excel file as shown below.

Excel Data Sample

Step 3 - SQL Server Destination Table

Let's create a destination table in which we will load the data after reading from all three Excel files. Please use the below SQL code to create the table.


USE [AdventureWorksDW2008R2]
GO
CREATE TABLE [dbo].[ImportMultipleExcelFiles](
	[EMPID] [int] NULL,
	[EMPNAME] [nvarchar](255) NULL
)
GO

Step 4 - SSIS Package Creation

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

Create SSIS Package

Step 5 - SSIS Package Variables

Please create a variable named FileName, scope of this variable is ImportMultipleExcelFiles, Data type is String. Please refer to the image below.

Create SSIS Variable

Step 6 - SSIS Foreach Loop Container

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

Add ForEach Loop Container

Step 7 - SSIS Foreach Loop Container Collection

Edit the Foreach Loop Container, in the Collection section change the Enumerator value to "Foreach File Enumerator". Please refer to the image below.

Edit ForEach Loop Container

We have to change the Enumerator configuration as shown below.

  • Folder: Provide a complete folder path location where all our Excel source files are stored. We have all Excel files stored in C:\Excel_Exercise\Excel_Source.
  • Files: We need to read the Excel files from our source folder, so please enter *.xls in the Files section, this will make sure our SSIS package will read all available .xls files from the source folder. Here * indicates that the Excel file name can be anything, but file extension will be .xls. If we need to read data from a specific Excel file name then we have to configure it accordingly.
  • Retrieve File Name: Please select the Fully Qualified radio button. Please refer to the below image for your reference.
Enumerator Configuration

To create variable mappings for the Foreach Loop container, select the "User::FileName" variable and set the Index value to 0 in the Variable Mappings section. Please refer to the image below.

ForEach Loop Container Variable Mapping

Step 8 - SSIS Data Flow Task

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

Add Data Flow Taskin SSIS

Right click on the recently added Data Flow task and click on Properties, please refer to image below.

Data Flow Task Properties

Please mark the DelayValidation property to True, please refer to the image below.

Data Flow Task Delay Validation

Step 9 - Excel Source in Data Flow Task

Add an Excel Source in the Data Flow Task and create a new connection to any of the Excel source files.

Excel Source Assistance

In my case I used the First_Excel_Souce.xls, please refer to the image below.

Excel Source Folder Path

Open the Excel Source Connection and make sure it is configured as the shown below.

Excel Source Editor

Step 10 - OLEDB Destination in Data Flow Task

Add an OLE DB Destination task in the Data Flow Task and create a connection to the destination database. Select the destination table (ImportMultipleExcelFiles) and map the available input source columns to the available destination columns. Please refer to the image below.

Add OLEDB Destination Task

After adding the OLE DB Destination task, your Data Flow Task should look like the image below.

Data Flow Task Source and Destination

Step 11 - Configure Dynamic Excel Source Connection

As of now our Excel source connection is fixed to one file named as First_Excel_Source.xls. We have to make the Excel connection dynamic so that it can connect to each Excel file in the source folder. To make the Excel source connection dynamic, please right click on Excel Source Connection and then click on Properties. You can refer to the image below.

Excel Connection Properties

Please expand the Expression Properties, then select the Connection String property and then click on the expression icon. Please refer to the image below.

Connection Properties Expression

Please copy the below code and paste it in the expression window.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+@[User::FileName]+";Extended Properties=\"Excel 8.0;HDR=YES\";"

Click on the Evaluate Expression button to sure it doesn't throw an error then click on the OK button. Please refer to the below image.

Connection String Expression

Step 12 - Execute SSIS Package

Let's execute the SSIS package, as you can see from the image below the SSIS package was executed successfully.

Execute SSIS Package

Step 13 - Review Final Data

Let's preview the data in the destination table. As you can see from the image below, the data from all three Excel files is loaded per our requirement.

Data Preview Destination Table
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


 









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     



Sunday, February 05, 2017 - 1:45:15 PM - oky prima Back To Top

 

thank you for this information.
My question is like Mrs. Haritha below. I want to import one file excel with multiple sheet and different destination in every sheet. do you have a solutions?

thank you.


Wednesday, September 21, 2016 - 5:48:10 PM - tina Back To Top

I have 64-bit windows 7 running 53-bit sql server 2012 and Office 2010 32-bit.

It does not like the connection string in step 11. Can you please help? Thanks.

 


Friday, September 16, 2016 - 12:35:09 PM - Haritha Back To Top

 

 I want to load excel files with multiple sheets. For example First_Excel_Source contains Sheet1 & sheet 2, Second_Excel_Source contains Sheet1 & sheet 2, Third_Excel_Source contains Sheet1 & sheet 2. Could you provide me the solutions?Thanks in advance.


Tuesday, February 23, 2016 - 3:55:10 PM - Lou Conova Back To Top

 Dear Ganesh,

We only use  SQL Server 2008 at work and it don't have the option 'Source Assistant - Add New Source' (step 9).

Looks like the Source Assistant is a new option in SQL2012 so will not be available in the versions we are using.

Do you have a work around for this step 9?

 


Monday, February 15, 2016 - 11:47:59 PM - Kris Maly Back To Top

 Dear Ganesh

Writing to Multiple WorkBooks not Tabs

Bikes dat in to one workbook

Clothing data to another workbook

Etc

 


Monday, February 15, 2016 - 5:20:20 PM - Pedro Faro Back To Top

Hi Koen,

Tank's for your answer.

I be able to import Excel Files (Excel Version 2007) , after installing 

Microsoft Access Database Engine 2010 Redistibutable

https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

Regards

 


Monday, February 15, 2016 - 4:05:31 PM - Koen Verbeeck Back To Top

Hi Kris,

another option would be to use SSIS.
You can fetch the different Product Categories and store them in an SSIS object variable (a recordset destination in the data flow).
Then you can loop over the different product categories using a for each loop.

Inside the for each loop, you have a simple data flow extracting the data for the current product category and writing it to an Excel file. If you put an expression on the Excel connection manager, you can write to a different Excel file each time.


Monday, February 15, 2016 - 4:03:26 PM - Koen Verbeeck Back To Top

Hi Pedro,

Visual Studio is a 32-bit application, that's why you need the 32-bit ACE OLE DB drivers to load Excel files with SSIS in Visual Studio.
However, if you want to run your packages on a server, you still need to have the 64-bit ACE OLE DB drivers.

You can install them side-by-side, by forcing the install through the command line.
More info:

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/force-ace-installation/


Monday, February 15, 2016 - 2:37:58 PM - Ghanesh Back To Top

Hi Kris Maly,

 

Below tip will solve your problem.

 

https://www.mssqltips.com/sqlservertip/3527/export-sql-server-reporting-services-report-data-into-multiple-excel-worksheets/


Sunday, February 14, 2016 - 3:43:06 PM - Kris Maly Back To Top

 Thanks

You seem to know lot of SSIS

Thanks for educating the community and appreciate your volunteership.

Question

I want to write data from Table to Multiple Excel wotk books in SSIS or SSRS can you help me?

Example 

Adventure Works database

Product Category

Product

Crete a group by Product Category and Export the each group data to each Excel file.

Is that possible and any idea?4

 

Thanks a lot 

 


Saturday, February 13, 2016 - 12:56:41 PM - Pedro Faro Back To Top

 Hi,

It's an intersting Example , but the example must be with EXCEL files >=2007 (XLSX))

If i selected "Excel 2007" don't work.

 

I googled it, and i need to install 32b Access driver (uninistall 64b driver) ..  there is no other solution ?

 

 Regards

 

Pedro


Friday, February 12, 2016 - 9:22:15 AM - Ghanesh Back To Top

 

Koen Verbeeck,

Yes, you are right! I missed this information.

Thanks for adding this information here.

Regards,

Ghanesh

 


Friday, February 12, 2016 - 2:54:19 AM - Koen Verbeeck Back To Top

You should mention a few of the limitations of SSIS when looping over Excel files:

* all of the Excel files should have the same metadata, e.g. having the same amount of columns with the same column names and the same data types

* the names of the Excel sheet with the data should always be the same


Learn more about SQL Server tools