Recently when using a foreach enumater to set an excel file path in a foreach loop in SSIS I ran in to a problem. In this case it was where the loop would act as if it were successfully processing several excel files, but the data would not change. I even put a script output on the data flow to write the filenames to a log to make sure I wasn't just running the same file over and over. Turns out on the excel connection manager properties.. The same place where you go to set the expression to change the excelFilePath for each iteration of the loop, there is a setting called RetainSameConnection.
Having this set to true was not allowing the expression to overried the file path.
I feel like in older versions of Visual Studio I was able to processed multiple files in a foreach loop without checking this box.
