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

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


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?


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]
CREATE TABLE [dbo].[ImportMultipleExcelFiles](
	[EMPID] [int] NULL,
	[EMPNAME] [nvarchar](255) NULL

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

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

Monday, July 26, 2021 - 4:17:44 AM - Hosein Back To Top (89047)
I was running sample without problem
but low speed ?
Can you Help me

Wednesday, July 7, 2021 - 8:25:47 AM - Simon Back To Top (88958)
I am a bit late to this, but I set this up as detailed and it worked fine for the first run. Thereafter the package would stop after the first file, but the loop & data flow tasks were still showing the yellow spinning cog. I eventually seem to have fixed this as follows...

1) Created template file with no records, just headers. I then set the full path and filename of this template file as the FileName variable's default value.
2) As suggested by Abhilash I set the excel connection's expression for the ExcelFilePath property rather than for the ConnectionString property.

It now appears to be working correctly every time, so just posting here to help anyone else that might be experiencing the same issues. FYI, this is on SQL Server 2017.

Thursday, February 11, 2021 - 6:19:10 AM - Ajay Back To Top (88218)
the schema for each of my excel file is different like they have different no of columns then how to upload the data to SQL.

Tuesday, January 12, 2021 - 6:22:46 AM - Archna Back To Top (88035)
Hi ,

When i am giving the filename dynamically in expression , it errored out .
i have followed the same steps but still excel connection manager is in error.

Wednesday, September 9, 2020 - 8:20:01 PM - Saif Back To Top (86447)
i have a problem when importing excel using ssis and add to sql agent, the process take very long time, but if using import from ssms this processes more quickly. any idea?

Thursday, April 16, 2020 - 2:22:26 PM - Neha Back To Top (85391)


Thanks for sharing its very useful. I have one case in my project. Can we read multiple excel files from multiple file paths. Pls suggest. It will be great help for me.

Tuesday, April 14, 2020 - 11:41:18 AM - Anurag Back To Top (85365)


Thanks for sharing. It's really useful. I have one one case where we have multiple excel file but sheet names are different for all the files. And we have to process that in single for each loop. Pls suggest. It will be a great help for me.

Regards, Anurag

Tuesday, March 24, 2020 - 3:48:54 PM - SHIVAM SHARMA Back To Top (85179)

The above solution is step by step and to the point and correct

those getting the problem of datatype conversion of a particular column, try to replace null values to blank in excel sheet, again try to run your package in SSIS.

write your own file path in expression, goto expression drag user file path to below box and click evaluate expression it will fetch the location given while

creating variable file-path and value (file path of the excel folder in the value) in the values box.

Wednesday, January 30, 2019 - 11:08:22 PM - juan Back To Top (78921)

Im having the following problem: in step 11, when I get into the Expression Builder I always get the window Variables and Parameters empty. (User::FileName is not available). How can I fix it?

Im going crazy.

Thursday, July 26, 2018 - 5:56:28 PM - Ria Back To Top (76840)


Thanks for this article. I am interested toknow how do you upload the latest excel from a folder into a table in the database?

Looking forward!

Thanks in advance

Saturday, March 17, 2018 - 8:51:28 AM - Abhilash Back To Top (75455)

Step 11: edit properties of excel connection and change expression to "excelfilepath" to evaluate for User::FileName instead of "connectionstring" this much will be enough no need to change connection string value.

Tuesday, February 27, 2018 - 11:52:51 PM - Dave Thul Back To Top (75310)


Hi Ghanesh.  Thanks for the well written instructions.  I followed everything but unfortunately, my Excel Source object does not seem to be respecting the delayValidation = True setting in the data flow task.  I am getting a validation error when I try to run it.  In fact, a red X persists in the Excel Source object.  Have you seen this before?  Thanks so much. 

Saturday, January 6, 2018 - 2:32:36 AM - Ranjith Back To Top (74849)

I did what you said above. But  i get some error. The error message is

"[Excel Source [16]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009.There may be error messages posted before this with more information on why the AcquireConnection method call failed. "

Please advise me. 

Friday, December 1, 2017 - 5:46:40 PM - Jon Bryant Back To Top (73544)

While this post has been out there for a while, I've been running into the same issue over and over and was hoping this would be the solution, but unfortunately it isn't.   I'm running Visual Studio 15 and SQL 16.  When attempting to pull data from multiple Excel files (even with the directions posted on this blog), it works perfectly when I test it in VS, pulling from a total of 24 Excel files (all set up identical, naming scheme and columns/data type).  When I convert to Package Deployment, then Project Deployment, and finally Deploy, all goes well.   

But once I deploy and go to execute the package within SSMS, it only pulls from the first file.  Again, even when starting the package over and following the steps here, all works well in VS but once it's deployed, SSMS only grabs the first file.

Have any of you experienced something like this and if so, would you happen to recall how you corrected it?

Thanks in advance for any help!


Tuesday, September 12, 2017 - 1:47:59 AM - Jai Sharma Back To Top (66192)

 Keep in mind about this step 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";'. Donot just copy and paste. first what connectionString appeared in properties when you hardcoded it for initiate it. than change it accordlingly in step 11. I was working on Visual Studio 2013 with excel version 2010 so I had to change like this

'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=                              ;Extended Properties="Excel 12.0 XML;HDR=YES";'

not like step 11 which is 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";'



Friday, April 28, 2017 - 6:51:10 AM - Kirti Back To Top (55273)

Hi! I did exactly the same you mentioned, but in my case only first file is getting imported & it is ending with error.

Error: component "Excel Source" (1) failed the pre-execute phase and returned error code 0xC0202005.



Sunday, February 5, 2017 - 1:45:15 PM - oky prima Back To Top (46045)


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 (43379)

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 (43347)


 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 (40757)

 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 (40689)

 Dear Ganesh

Writing to Multiple WorkBooks not Tabs

Bikes dat in to one workbook

Clothing data to another workbook



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

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



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

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 (40685)

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:

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

Hi Kris Maly,


Below tip will solve your problem.

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


You seem to know lot of SSIS

Thanks for educating the community and appreciate your volunteership.


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


Adventure Works database

Product Category


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 (40672)


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 ?





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


Koen Verbeeck,

Yes, you are right! I missed this information.

Thanks for adding this information here.




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

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

get free sql tips
agree to terms