?
Solved

Using the same data in multiple Data Flow Tasks

Posted on 2008-11-13
10
Medium Priority
?
1,132 Views
Last Modified: 2013-11-10
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?
0
Comment
Question by:RAFUser
  • 4
  • 4
  • 2
10 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22950488
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
 

Author Comment

by:RAFUser
ID: 22950570
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22950815
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:PedroCGD
ID: 22950831
ok... the schema is the same in all source files?!
0
 

Author Comment

by:RAFUser
ID: 22950891
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22951015
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22951051
and why you dont have the 5 different sources in the same dataflow and make all the transformations to insert in the destination?!
0
 

Author Comment

by:RAFUser
ID: 22951077
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
 

Author Comment

by:RAFUser
ID: 22951107
Hogg, I will probably just end up using temp tables.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22951307

"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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question