Using the same data in multiple Data Flow Tasks

I have a situation where we need to read data from multiple source files and merge it all together to form one destination file.  Since the existence of any of those flat files can be flaky, we want to have several Data Flow Tasks - one for each file to load it to memory, and one to actually do the merging.  This will allow us to throw an error and determine which specific file wasn't available.  However, we can't find the best way to "transfer" data from one data flow task to another.

Our first attempt was to load the source file to a Recordset destination, assign it to a variable, and use it in the final Data Flow Task, but we found out that there aren't Recordset sources.  After doing research, we found that we may want to load the source file to a Raw File Destination, but this appears to actually create a file on disk.  I'm not so sure we would gain much from using this approach.

This doesn't seem like a request that is out of the ordinary.  How do other people use the same data in multiple Data Flow Tasks?
RAFUserAsked:
Who is Participating?
 
HoggZillaConnect With a Mentor Commented:
You can use a script component in the Data Flow. http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/
I still think you should use a temp table, even if it is just for processing. However, if that is not an option, then I believe you're best answer will be to load the files into a variable. Not an object variable, a string variable. Then use a data flow Script Component Source to read from your variables and create the source data. I am attaching a file that shows how to load a variable with a file.

SSIS-Populate-Variable.pdf
0
 
HoggZillaCommented:
You can use an Object variable to hold the recordset.
Is there a valid reason not to use a temp table in the database? Sure would make it easy. Load it, Write from it, Truncate it?
0
 
RAFUserAuthor Commented:
I am not using a temp table in the database because we aren't using the database at all for this.  We are importing from several files and exporting to another file.

We set up our Recordset to save to an object variable... now, how can I access that from a different Data Flow Task?  There is no Recordset Source.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PedroCGDCommented:
You dont have recordset source... you are correct... but you can add a for each loop container to get the values from recordset and save each time execute the loop in a SSIS Variable...
But you need to have a source inside dataflow... and you need for several dataflows...

Soo... I suggest to create a temp table to do your job as HoggZilla also suggest.
regards!
Pedro
0
 
PedroCGDCommented:
ok... the schema is the same in all source files?!
0
 
RAFUserAuthor Commented:
No, there are 5 different source files with 5 different structures.  I wanted to have 5 different Data Flow Tasks load each individual file to an in-memory recordset, and then have a 6th Data Flow Task perform several Merge Joins on the in-memory recordsets.
0
 
PedroCGDCommented:
and why you dont have the 5 different sources in the same dataflow and make all the transformations to insert in the destination?!
0
 
RAFUserAuthor Commented:
Pedro, read the first paragraph in the original message:

"Since the existence of any of those flat files can be flaky, we want to have several Data Flow Tasks - one for each file to load it to memory, and one to actually do the merging.  This will allow us to throw an error and determine which specific file wasn't available.  However, we can't find the best way to "transfer" data from one data flow task to another."
0
 
RAFUserAuthor Commented:
Hogg, I will probably just end up using temp tables.
0
 
PedroCGDCommented:

"This will allow us to throw an error and determine which specific file wasn't available"

You can control it inside one dataflow... using the red line output (error)...
I'm asking it, because you have several advantages to use it in the same dataflow... and I'm here for free to help you try to achive your goals!! So, let me know if you need my help and an SSIS package example!
Regards!
pedro

www.pedrocgd.blogspot.com
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.