SSIS Pass Datasource Between Control Flow Tasks

问题:

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.

回答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.

回答2:

A Data Flow Task needs to have a destination; a Data Flow Task likewise is NOT a destination. Otherwise the data doesn't go anywhere in the pipeline. From my experience, your best bets are to:

1) Pump the data into staging tables in SQL Server, and then pick up the validations from there. 2) Do the validations in the same Data Flow task.