Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - How to pass data set from one Data Flow to another? (or other options?) Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 11:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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.
Post #1566071
Posted Wednesday, April 30, 2014 8:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 406, Visits: 10,844
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.
Post #1566437
Posted Wednesday, April 30, 2014 8:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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.
Post #1566439
Posted Wednesday, April 30, 2014 9:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 406, Visits: 10,844
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?
Post #1566443
Posted Wednesday, April 30, 2014 9:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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.
Post #1566445
Posted Wednesday, April 30, 2014 9:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 406, Visits: 10,844
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.
Post #1566452
Posted Wednesday, April 30, 2014 9:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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.
Post #1566456
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse