Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

SSIS - Flat File Connection Manager using Variables

Discussion in 'SQL Server Scripts and Tools' started by Peter Schmitz, Feb 6, 2017.

  1. Peter Schmitz

    Peter Schmitz Administrator 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:

    upload_2017-2-6_14-22-27.png

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

Share This Page