SSIS Foreach Loop Container Not Looping through Excel Files

Question

The Foreach Loop container in my process isn't pushing the new filename into the established variable. It loops through the process as many times as there are files that meet the criteria, I just need the file name to be dynamic.

I have created a variable name that contains the full filepath of the first file in my desired directory. Looks something like C:\Somepath\ExcelFile.xlsx I have also created a variable name ExtProperties to be used in the ConnectionString with the value "Excel 12.0;HDR=Yes"

The Foreach Loop Container has the following settings:

:

1. The Enumerator is set to the Foreach File Enumerator
2. The Folder is the directory location of my files
3. The Files is currently set to *.xlsx
4. Retrieve file name is set to Fully Qualified

The ExcelFileName variable I mentioned previously has been set at Index 0

I've created an Excel connection manager pointing to the initial file with the following relevant properties:

1. DelayValidation: True

2. Expression: I have tried both setting the ExcelFilePath to the ExcelFileName variable and using the following for the ConnectionString:

   "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFileName]   + ";Extended Properties=\"" +  @[User::ExtProperties] + "\""
   

Right now it is using only the ConnectionString.

3. Retain Same Connection: False

The data flow is using an excel source using the excel connection manager. The purpose of the dataflow is to pull the number of records from each excel file, get the name of the file and the user performing the load, and push the information into the DB. When pushed out to the DB however, the filename and record count is constantly the first file used, the same number of times as however many files meet the criteria.

I get no error messages or warnings. I have used the following script in my control flow to see if the value of the variable has been changing, but the message box popping up shows that I still get the initial value.

MessageBox.Show(Dts.Variables["User::ExcelFile"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;

I've been reading threads about this for days and these were the settings that were proposed to work, but this is still an issue for me. Any help would be appreciated.

Answer 1

From the first image, it looks like you have set the Variable ExcelFileName to be evaluated as Expression since the expression mark (fx) is shown on the variable icon:

Just remove the expression from the variable and check that the EvaluateAsExpression property is set to False