DTS SQL to Oracle

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dave Barribeau (anonymous_at_discussions.microsoft.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 11:57:42 -0700

Every night I need to upload the transaction records from
an SQL table to a corresponding Oracle table. The tables
are identically structured except that the SQL table has
a UPLOAD field that I change to 'Y' once that record is
uploaded (can do it with one final query when all records
have been updated).

There will occasionally be a few records in the SQL table
that will cause a duplicate key violation (Invoice Number
field) when they are inserted into the Oracle table. If
this happens, I need to update the non-violating fields
in the Oracle record.

What is the best (fastest) way to do this? I am writing
in VB using the DTS Objects. This is my first attempt at
controlling DTS with VB. Is there an event that will be
raised on the insert failure where I can then perform the
update instead?

Relevant Pages