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!
2017 Magic Quadrant for BI
Find out if the tool you are considering is a leader!Read The Magic Quadrant Report