SSIS - Flat File Connection Manager using Variables

Peter Schmitz

Staff member
I ran across another minor issue with the Flat File Manager today. I set up a ForEach loop to go through all files in a folder, load them, and subsequently archive them. This uses a number of variables to set different parts of the process dynamically.

When executing the loop, though, the sourcing seemed to fail once the loop finished once. After some frustrating time spent figuring out why things were not working, I pinpointed the issue to the file-name variable used as output within the For Each loop.

When you attempt to use this approach, make sure that the variable is not set to be an expression, as this will cause Visual Studio to evaluate whatever the expression contains as a default as the actual filename, and thus the load will fail because the file is no longer there.

You can tell the difference between a regular variable and expression by the icons in front of the name:


The first variable is an expression. The second is not. You want the filename placeholder to NOT be an expression.