sql - SSIS Pass Datasource Between Control Flow Tasks

Question

I'm having troubles solving this little problem, hopefully someone can help me.

In my SSIS package I have a data flow task. There's several different transforms, merges and conversions that happen in here.

At the end of the dataflow task, there is two datasets, one that contains two numbers that need to be compared, and another dataset that contains a bunch of records.

Ideally, I would like to have these passed onto a whole new data flow task (separate sequence container) where I can do some validation work on it and separate the logic.

I cant for the life of me figure out how to do it. Iv tried looking into scripting and storing the datasets as variables, but I'm not sure this is the right way to do it.

The next step is to export the large dataset as a spreadsheet, but before this happens i need to compare the two numbers from the other dataset and ensure they're correct.

Answer 1

To pass data flowing in one dataflow to another, You have to have a temporary location.

This means that You have to put data in destination in one dataflow and then read that data in another dataflow.

You can put data in number of destinations:

• database table
• raw file
• flat file
• dataset variable (recordset destination)
• any other destination component that you can read from with corresponding source component or by writing script or whatever

Raw files are meant to be used for cases like this. They are binary and as such they are extremely fast to write to and read from.

In case You insist to use recordset destination take a look at http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx because there is no recordset source component.