ForEach Source Flat File Loop Container does not retain file mask, but overwrites with first filename and processes only that file.
Friday, December 07, 2012 9:02 PM
I have a foreach source flat file loop container in which I put the default folder name and the file mask, *pdc.dat. I have created a variable called strFFSourceFileName for filespec, and strFFSourceFolder for directory. All variables are package level. In the Foreach Loop editor, I have put for folder, let's say, C:\MyFolder\ and the filespec *PDC.dat. I configured them in the Collection tab of the ForEach Loop Editor under expressions for Folder and Filespec respectively and mapped them to strFFSourceFolder and strFFSourceFileName. For the value field of the variables I put the first filename and the folder name respectively. I tried putting the mask value, *PDC.dat, but that didn't work.
I also created a variable called strSourceFileName and set Evaluate as Expression to True and entered the following value to it: @[User::strFFSourceFolder]+@[User::strFFSourceFileName]. I clicked to evaluate as expression and verified that it had the complete path and filename in it.
In the connection managers, I configured the first of the flat files (which have identical formats) and previewd it. Under expressions in the ConnectionString field of the connection manager just created, I put the value @[User::strSourceFileName].
I have done this on other packages which work fine. In this one, when I run it, the first filename is run perfectly, but the loop does not run any more. Moreover, in the ForEach Source Flat File Loop container, the filename field is overwritten with the first filename (abPDC.dat) and no longer has *PDC.dat.
I am sure I am not very clear in this description, but any tips will be appreciated. The bottom line is that the loop is not looping, but only processing 1 file.
Friday, December 07, 2012 10:54 PM
What is the variable that you are mapping the output of the ForEachLoop? It isn't strFFSourceFileName I hope. You need to map the file name in the Variable Mapping tab to some other variable than the one that you use for the file spec.
Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com
Monday, December 10, 2012 3:24 PM
As I stated above in the following quote:
"I also created a variable called strSourceFileName and set Evaluate as Expression to True and entered the following value to it: @[User::strFFSourceFolder]+@[User::strFFSourceFileName]. I clicked to evaluate as expression and verified that it had the complete path and filename in it."
strSourceFileName (without the "FF") is the entire path with the filename. I have done everything I can find to have it process more than one file, but it just doesn't.