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.
- 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 Gartner Magic Quadrant for BI Report
Get the full report on the top BI Vendors.Get the Report