SSIS Data Flow Update

I am trying to figure out the best way to a开发者_开发百科pproach the following scenario.

Overview

Payments are collected over the course of the day and stored in a table (SQL Server). At the conclusion of each day, these payments are to be exported and inserted into another database (Oracle). The payments that were successfully inserted into the Oracle database are to be updated with a time stamp.

Process

Step 1 - Each payment record has to be matched against the Oracle database to ensure that a value referred to by the payment record exists in the Oracle database. Payments that satisfy this step move down the pipeline.

Step 2 - Payments are inserted into the Oracle database, with any insert errors being redirected to a recordset.

Approach

I am using an OLEDB Data Source for the payments (SQL Server) and for the lookup values (Oracle) and then using a Merge Join (Left Outer) to append the data to the payments. Using a conditional split from the results of the Merge Join, I pass the payments that matched the lookup data down into the OLEDB Destination.

I need to update the original payments table with only the payments that made it down to the insert. Using the Conditional Split, I can determine which rows were not matched in Step 1, and redirecting rows on the error output on the final insert I can determine which rows failed there. However, I am not seeing the best way to determine the payments that were successfully inserted and the best way to update the original table.

I would probably insert a list of ids for successful payment records into a temp table in the original db (using a multicast + OLE DB Destination) and then run a stored procedure or use an SQL Query task to update the original table based on the values in the temp table. Then you can truncate or drop the temp table when you're done.