I am working in BIDS. I have an SSIS package that loops over all files in a given directory and imports all of them to a table. So, my control flow has a Foreach Loop Container with a Data Flow Task inside of it. The Data Flow Task goes straight from a Flat File Source to an OLE DB Destination. Because the directory can change, I have made the Foreach Directory dynamic (i.e., I gave it a package variable, which I can change when the directory changes). The Files field has something like stuff*.pip, such that all files in the directory that match this pattern get imported. The Variable Mappings tab has a file name variable, which is the same variable used in the Expressions of the flat file connection manager.
When I originally set up the flat file connection manager, I had to point it to an already existing file to pick up the file's metadata. I then changed the file connection manager to dynamic (i.e., I added a file name variable in the Expressions property) such that the Foreach Loop Container will pick up all files (that match the pattern).
Since then, the metadata on the file has changed. So, when I open the solution file, it throws a warning on the Flat File Source in the Data Flow Task. To resolve this, I have to temporarily change the flat file connection back to a hard-coded path such that I can change the metadata on the new file. I then make it dynamic again (i.e., use the file name variable in the Expressions, which overrides the hard-coded file path). (Finally, I double-click on the Flat File Source in the Data Flow Task to automatically update the metadata in the Flat File Source.)
Is this the correct way to update the metadata on a flat file? It seems a bit clunky to me.
