Sometimes we have many excel files that contain the data we have to work with. When those excel file have the same structure, I mean, the same numbers of columns, same name of columns, same file´s extension, etc, and the only thing which is different is a partial part of the file name, and all of them are in the same folder, the option that SSIS offers us is a shape that help us to repeat task in a package.

In this example I am going to show how to configure the Foreach loop container and the connection manager to achieve our goal.

So, in my case I have 4 excel files. The structure is the same: 3 columns named: A, B and C and these columns contain numbers.

 

These files are located in the same folder named: Exceles, in that way all have the same folder path.

 

The first thing we have to do, in the order I am describing here in the following:

• Create a variable with data type: String. In the field value you can write by default a path for one excel file.

 

Then, create an Excel Connection Manager, and in the properties select true in Delay validation. This is going to help us when we have to use the variable that is going to have the file path for each file in the folder, and the connection is going to work without any problem.

 

Now, we select in the connection manager properties windows: Expressions, and select the points ….

In the Property Field select: ConnectionString.

 

In the field, for this case I am using this provider and this variable:

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + @[User::CurrentExcelPath] + “;Extended Properties=\”Excel 12.0 XML;HDR=YES\””.

When you hit the Evaluate Expression, notice that the Evaluated value shows the entire file path that we assigned to the variable created before.

 

Then Ok, and the fx expression appears in the connection manager that indicates that we are using a parameter or expression for that connection string.

 

Test the package and make sure that all the data in the excel file now are in the destination you have selected.