dataflowtask - SSIS Data Flow Update -

i trying figure out best way approach following scenario.

overview

payments collected on course of day , stored in table (sql server). @ conclusion of each day, these payments exported , inserted database (oracle). payments inserted oracle database updated time stamp.

process

step 1 - each payment record has matched against oracle database ensure value referred payment record exists in oracle database. payments satisfy step move down pipeline.

step 2 - payments inserted oracle database, insert errors being redirected recordset.

approach

i using oledb data source payments (sql server) , lookup values (oracle) , using merge join (left outer) append data payments. using conditional split results of merge join, pass payments matched lookup data down oledb destination.

i need update original payments table payments made down insert. using conditional split, can determine rows not matched in step 1, , redirecting rows on error output on final insert can determine rows failed there. however, not seeing best way determine payments inserted , best way update original table.

i insert list of ids successful payment records temp table in original db (using multicast + ole db destination) , run stored procedure or use sql query task update original table based on values in temp table. can truncate or drop temp table when you're done.