A few weeks ago I had to create a foreach container in an ETL package to loop over subdirectories of a folder. There are many tutorials out there for this purpose:
In each iteration of the loop some files of each subdirectory where read. And since the path of these files were created at run time and the XML Source I used in my data flow requires a file path at design time and to start without an error, I just entered a filename into the variable (I called it Users::Filename) that was available on my computer. At runtime when entering the loop, this value would be replaced by the correct value. As long as the packages were tested on my computer and the dummy file was available, this would work. A few days later when it was tested on another computer, the program didn’t execute because it couldn’t find the file it actually didn’t need. Then I noticed that my not-so-clean solution was actually quite dirty 😉
First I tried to think about some workaround but nothing seemed to be easy and clean. Then I realised that when so many people were using loops (and I found many tutorials indicating so), there must be a nicer solution for that. The whole SSIS concept would be useless if you had to know each file at design time. I found the solution, like most of the time, on stackoverflow. The XML file source flow has a property ValidateExternalMetadata and that can be set to False.
Ok, so this makes it possible to leave the variable User::Filename empty at design time and you don’t need a dummy value.
Big drawback: When something changes in your XML file or when you need to adapt anything, you need to use a valid path to an existing XML file and turn on ValidateExternalMetadata again, otherwise Visual Studio can not provide you that information (of course!).