Ignore duplicate records in SSIS' OLE DB destination

Question

I'm using a OLE DB Destination to populate a table with value from a webservice.

The package will be scheduled to run in the early AM for the prior day's activity. However, if this fails, the package can be executed manually.

My concern is if the operator chooses a date range that over-laps existing data, the whole package will fail (verified).

I would like it:

• INSERT the missing values (works as expected if no duplicates)
• ignore the duplicates; not cause the package to fail; raise an exception that can be captured by the windows application log (logged as a warning)
• collect the number of successfully-inserted records and number of duplicates

If it matters, I'm using Data access mode = Table or view - fast load and

Suggestions on how to achieve this are appreciated.

Answer 1

That's not a feature.

If you don't want error (duplicates), then you need to defend against it - much as you'd do in your favorite language. Instead of relying on error handling, you test for the existence of the error inducing thing (Lookup Transform to identify existence of row in destination) and then filter the duplicates out (Redirect No Match Output).

The technical solution you absolutely should not implement

Change the access mode from the "Table or View Name - Fast Load" to "Table or View Name". This changes the method of insert from a bulk/set-based operation to singleton inserts. By inserting one row at a time, this will allow the SSIS package to evaluate the success/fail of each row's save. You then need to go into the advanced editor, your screenshot, and change the Error disposition from Fail Component to Ignore Failure

This solution should not used as it yields poor performance, generates unnecessary work load and has the potential to mask other save errors beyond just "duplicates" - referential integrity violations for example