SSIS: Import multiple Excel files into database with Foreach Loop Container

I wanted to import more than 60 Excel files into a database table the other day. Foreach Loop Container really helped to perform the task.

I started with creating an Excel Connection Manager. I hard coded the file path to make the BI designer happy. Then in the Properties window of the Excel Connection Manager, I set the “ExcelFilePath” and/or “Server” to the value of the pre-defined user variable. I probably only need to set “ExcelFilePath”, but anyway I just wanted to make these values consistent.



Then I dragged a Foreach Loop container onto the Control Flow. From the Properties window of a Foreach Loop Container:
1) Foreach File Enumerator.
2) Hard coded the Folder path to please the designer, and set the file mask and the return value.
3) Added an Expression so that the value of “Directory” is actually set by a user variable I defined earlier. One thing that bugs me a litter is – it is called “Folder” on the screen but ‘Directory” in the Expressions list.



4) In the Variable Mappings section, I set the return value to the user variable.



Now I could drag a Data Flow Task into the Foreach Loop Container, and load data from an Excel file to a database table. Of course the Excel Data Source uses the Excel Connection Manager I created in the first step.