Unanswered: OLE DB Source with a Variable filename
I'm sure this is simple, but for the life of me I can't see how to do it.
I have an OLE DB Source with an OLE DB Connection. I have a large number of CSVs that have a naming convention including the date. I want to be able to iterate these files and have the OLE DB Source connect to each file in turn.
I have created a For Each Loop Container to iterate the CSVs in the directory. The filename goes into a Variable @USER::filename. What I can't figure out is how to have the OLE DB Source use the variable filename.
Also; the OLE DB Source seems to rely on having a valid filename in order to pass pre-execution validation. It also gets more complicated because changing the file can invalidate the column mapping and screw up all of the components downstream as the linage IDs will no longer exist.
What is best practice here? I'm starting to wonder if it isn't to use something other than SSIS..!
Did you consider batch processing the csv files with bcp or bulk insert?
Unfortunately the CSVs cannot be data typed until they are conditionally split. And I have a lot of other stuff to do....transforms that are more of a job for SSIS. It just drives me crazy that SSIS uses a linage ID that gets completely screwed up all the time.
I did manage to get around the problem I had, but I had to create a csv with a single row in order to maintain SSIS mappings. I've named the file data.txt with a variable named as such and then iterated for *.csv using the variable. This allows me to process all the CSVs and still have the single row sitting in data.txt so that SSIS doesn't get screwed up. I think this is probably about as elegant a solution as I'm going to get.