SSIS For Each Loop crashes the flat file connection

Question

I created a simple SSIS package to import a flat file (.txt) into a database table. Tested that and it works perfectly. Since I have several files to import, I added a foreach loop to go through all the files, added the variables as recommended in several examples found on the net but now my flat file connection manager returns an error of "A valid file name must be selected." and the package will not run. I have so far been unsuccessful in finding the solution for this issue and would appreciate any suggestions by the SSIS gurus of this forum. Many thanks in advance!

Here is what I have in the way of variables:

• SourceFileFolder which is the path to the folder that contains the files
• FileName a string containing one of the names of the files I am seeking to import
• SourceFilePath which is an expression driven variable that incorporates the previous two variables concatenated together. I can click "Evaluate Expression" and copy and paste it into windows explorer and open the file
• ArchivePath which is an expression driven variable that creates the path to archive the file to once it is processed.

Answer 1

As the message says this is related to your connection manager not gathering the connection string. This can be handled using the following:

First of all clear the expression on the SourceFilePath variable.

With your Foreach Loop Container, set it up as follows:

This will use your variable SourceFileFolder as the Folder, you could also just hardcode the folder name C:\ for instance. Also make sure your folder is qualified correctly, I.E. make sure it finishes with a slash C: won't work but C:\ will work.

Next you need to map the fully qualified name to your other variable SourceFilePath

This should now store the full name of the file the loop has found into the SourceFilePath variable. For Instance C:\File.txt, you can now use this as a connection string expression on your file connection manager.

Under the properties of the connection manager make sure the expression is set to ConnectionString and then use the SourceFileName variable.

ALSO MAKE SURE DELAY VALIDATION IS SET TO TRUE

This hopefully should mean you can loop through the files.