April 29, 2014 at 11:39 am
I am developing a package that needs to be able to export any of 5 different queries (all with different schema) to any of 4 different file formats. Both the export ID and the file format ID are parameters passed in to the package at run-time.
Right now, I have 5 different Data Flow tasks that run conditionally based on the export ID that's passed in. I had planned to likewise conditionally send the results of the query to one of four File Destinations inside each Data Flow task, using a Conditional Split. The problem with this is that SSIS isn't really able to realize that the logic I've put into the Conditional Split doesn't make each File Destination exclusive, so it has to verify and create each file, even though only one file actually gets written to. Ultimately, the problem with this is that my requirement says the filename has to be Export-MM-DD-YYYY.txt, regardless of which format is selected. So, the package errors out when it tries to validate each File Destination stating that, obviously, the file is currently in use. Unfortunate, but I don't really blame SSIS too much for this.
So, I was trying to think of ways to get around this...and thought that maybe if I could get the result set out of the Data Flow task, I could use the Control Flow's conditional outputs to go to another Data Flow task, based off of the file format ID, and only need to create one file. However, I don't know how to do get the result set out of the first Data Flow task..I thought of using the RecordSet destination and save it to an object variable, but it seems like the only way to then get it back would be using a loop, and that seems very impractical to me.
Any ideas? I'm willing to rethink the whole approach, if need be.
April 30, 2014 at 8:56 am
I would see if setting the DelayValidation property to true on each of the file connection managers solved this problem first. Then. if that did not work, I'd have each of the file destinations write to a differently named file and add an extra File System Task at the end of all the processing to perform a "Rename File" operation on the file to give it the required file name.
April 30, 2014 at 8:57 am
Yeah, I tried doing delayed validation, but it still tried to create the file. Unfortunately, the requirement is that the filename has to be the same. It can't change from one file format to another.
April 30, 2014 at 9:03 am
Could you have each of the 4 data flow tasks output to a differently named file and then add a File System Task at the end to rename the file to the name it needs to have?
April 30, 2014 at 9:06 am
Well if I'm doing 4 different data flow tasks, then I could conditionally specify to only go to one of them, the other 3 wouldn't get executed, and the filename problem goes away.
April 30, 2014 at 9:11 am
Sorry, from reading your original post I thought you already had a number of different data flow tasks that were trying to write to the same file name.
April 30, 2014 at 9:18 am
Ah, well yes, I do. Unfortunately, each different data flow task represents a completely different export. So, if I were to also create a data flow for each file format, I'd end up with 20 different data flow tasks. Ugh.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply