4

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.

craig
  • 25,664
  • 27
  • 119
  • 205

2 Answers2

6

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
1

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).

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 2
    SQL Server's [MERGE](https://msdn.microsoft.com/en-GB/library/bb510625.aspx) statement can be very handy for the merge from staging to destination when you're doing it like this. – Matt Gibson Dec 14 '15 at 20:13