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,

1. Number of columns
2. Name of column headings
3. Name of tabs

How to Import Multiple Excel files to SQL

1. Open Visual Studio
2. Create a new project

 

3. Select Integration Services Project

 

4. Create a Variable

• Name: FilePath
• Data type: String
• Value: blank

 

5. Drag and drop Foreach Loop Container to the Control Flow

 

6. Right click on the Foreach Loop Container then click Edit

 

7. 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”

 

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

 

 

9. Drag and drop the Data Flow Task in the container

 

10. Double click the Data Flow Task

11. Drag and drop Excel Source in the data flow

 

12. Right click > Edit

 

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

 

14. Select Name of the Excel sheet and click OK

 

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

 

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

 

17. Click “…”

 

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

 

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

 

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

 

21. Change DelayValidation option to True for Data Flow Task property

 

22. 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!