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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Steve HoggITCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.