How to Import Data from Multiple Excel files to SQL using SSIS & Visual Studio
I was asked to import data to SQL from multiple (15) Excel files, and each Excel file contained historical data with more than 200,000 rows.
I could do it with 15 data flows and Excel connections in SSIS, but I didn’t want to do it that way because I wanted to find a more efficient way than creating 15 data flows and Excel connections. I’m too lazy to create all those data flows and Excel connections! That would take a lot of time. So I Googled it.
I found many articles, but each article was slightly different and used a different approach to handle the situation.
I followed steps in several articles and Youtube videos, but I had no luck. Every time something went wrong or I was missing something. I scratched my head.
After a while, I found a way to make it work and I’d like to share it with everyone to save your time.
Here are my step by step instructions to create a SSIS package for importing data from multiple Excel files to SQL with only one data flow and one Excel connection.
For example, I have two Excel files at C:\BI\SSIS\Import Hist Data\Source Files\Test

Very Important Note
Ensure that each Excel file has the same schema.
For example,
- Number of columns
- Name of column headings
- Name of tabs

How to Import Multiple Excel files to SQL
- Open Visual Studio
- Create a new project

- Select Integration Services Project

- Create a Variable
- Name: FilePath
- Data type: String
- Value: blank

- Drag and drop Foreach Loop Container to the Control Flow

- Right click on the Foreach Loop Container then click Edit

- Go to Collection > Enter the folder path > Enter the file extension
Make sure that Enumerator is “Foreach File Enumerator” and Retrieve file name is set to “Fully qualified”

- Go to Variable Mappings > Select the User:FilePath variable > Click OK to close the editor


- Drag and drop the Data Flow Task in the container

- Double click the Data Flow Task
- Drag and drop Excel Source in the data flow

- Right click > Edit

- Click New > Enter one of your Excel file path > click OK

- Select Name of the Excel sheet and click OK

- Drag and drop the OLE DB Destination > Enter your SQL server information and select the target table

- Very important! Select the Excel Connection Manager > Click expand button for Expression

- Click “…”

- Select ExcelFilePath then Click “…” for Expression from its property

- Drag and drop the “User::FilePath” Variable then click OK

- Click OK. You will see an error markwe on the Excel Source. Don’t worry.


- Change DelayValidation option to True for Data Flow Task property

- Ignore the error mark from Data Flow Task then click Start


That’s it. I hope that I saved you some time importing your multiple Excel files into SQL!
Get the full report on the top BI Vendors.