NickName:dmarkez Ask DateTime:2012-06-12T12:10:36

SSIS: recordset or temp table

I have an SSIS application that needs to get data from 2 databases of different servers (not link). I need to get the match names and DOB records between 2 database then use the results to insert/update a table. My initial approach is to use OLE DB source then Merge Join and put the results to recordset. Then on controlflow, use the results of the recordset to insert/update a table. But I can't see the recordset at the control flow. Alternative solution is to create temp tables. But the temp tables are not visible since they reside at the tempdb database of each servers. What is a better approach for this problem?

Copyright Notice：Content Author:「dmarkez」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/10990421/ssis-recordset-or-temp-table

Answers

Diego 2012-06-12T19:25:50

what do you mean by put the results to recordset?\n\nIf you join two sources on the data flow using a join, that \"recordset\" on the join will only be available during the current dataflow. You cant use it on the control flow after the data flow is finisehd.\n\nwhy cant you just insert the resultset on the destination DB? You can perform any other transform operation on the same data flow and insert the result on the destination database. \n\nOr, if you really need to do something that can only be done on the control flow before insert the data, you can yes, insert the recordset on a temp table on the destination using a oleDBDestination and access in on another dataflow (not a very good approach, though)",