Search code examples
sql-serverssissql-server-2012ssis-2012

Ignore duplicate records in SSIS' OLE DB destination

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

enter image description here

Suggestions on how to achieve this are appreciated.

Solution

  • Here's how I would do it:

    • Point your SSIS Destination to a staging table that will be empty when the package is run.

    • Insert all rows into the staging table.

    • Run a stored procedure that uses SQL to import records from the staging table to the final destination table, WHERE the records don't already exist in the destination table.

    • Collect the desired meta-data and do whatever you want with it.

    • Empty the staging table for the next use.

    (Those last 3 steps would all be done in the same stored procedure).