Post reply

Pass data between dataflows

  • January 27, 2009 at 3:49 pm

    #199469

    Hi all,

    I have a dataflow (We'll call it Audit) which performs some audits and sends the "good records" to one flow and the bad records to an output file.

    I have a second dataflow (We'll call it Processing) which needs to process the "good records" from the previous (Audit) dataflow. I've found three ways which I can accomplish this:

    1) Audit dataflow creates temp SQL table in DB and loads records in this table. Processing dataflow uses the temp table to process the records and drops the table when it's done.

    2) Audit dataflow's destination is a Recordset object. Processing dataflow uses a script component as its source and loads datafrom recordset variable (kinda time consuming since I have to create and define all the fields).

    3) Do it all in one dataflow (not very clean looking)

    So my question is this: Is there a way that I can simply create 1 dataflow then use it in a different dataflow without having to resort to one of the above methods?

    Thanks,

    Strick

  • January 28, 2009 at 1:04 pm

    #934105

    stricknyn (1/27/2009)

    Hi all,

    I have a dataflow (We'll call it Audit) which performs some audits and sends the "good records" to one flow and the bad records to an output file.

    I have a second dataflow (We'll call it Processing) which needs to process the "good records" from the previous (Audit) dataflow. I've found three ways which I can accomplish this:

    1) Audit dataflow creates temp SQL table in DB and loads records in this table. Processing dataflow uses the temp table to process the records and drops the table when it's done.

    2) Audit dataflow's destination is a Recordset object. Processing dataflow uses a script component as its source and loads datafrom recordset variable (kinda time consuming since I have to create and define all the fields).

    3) Do it all in one dataflow (not very clean looking)

    So my question is this: Is there a way that I can simply create 1 dataflow then use it in a different dataflow without having to resort to one of the above methods?

    Thanks,

    Strick

    There are 2 more ways:

    1. Direct your good records to "raw destination component". You can then load the these records using "raw source component". The shortcoming is that you have to have enough drive space to create this temporary intermediate file and the process is sequential and the data is not processed in parallel.

    2. There is a much better approach. CozyRoc provides commercial components, which solve exactly that kind of question how to transfer data from one data flow to another as efficiently as possible. The components are Data Flow Source and Data Flow Destination. The data is transferred in-memory without a need for intermediate step and the data is processed in parallel, meaning if you have multiple CPU/cores machine, it will use the resources.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 28, 2009 at 1:26 pm

    #934124

    Interesting,

    Unfortunately I can't invest in any third party products. I'll try the raw file source/destination part and see how that works out.

    On a side note, do you know if this was solved in SQL Server 2008 (SSIS 2008)?

    Thanks for your help,

    Strick

  • January 28, 2009 at 1:34 pm

    #934138

    stricknyn (1/28/2009)

    Interesting,

    Unfortunately I can't invest in any third party products. I'll try the raw file source/destination part and see how that works out.

    On a side note, do you know if this was solved in SQL Server 2008 (SSIS 2008)?

    Thanks for your help,

    Strick

    No. Microsoft is not going to implement a different solution any time soon because for them the raw source/destination solution is good enough.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply