SSIS 2005 - Ignore row insert failures

Question

Hi,

I would like to ignore the errors that may occur when a batch is commited. In my case, unique columns.

The OLE DB Destination Error Output is set to "Ignore failure" but it is still failling. The Data Flow "stop on failure properties" are set to false and the MaximumErrorCount to 0.

I don't want to do row redirection to be able to keep the fast load mode.

Thank you

Answer 1

A few comments:

1. You can't use ignore errors because ignore row errors still passes the records to the destination. You have to use redirection to get rid of the bad rows.
2. If you don't want to keep a copy of the bad rows, then you can send them to a Row Count transformation since that has minimal performance impact. Alternatively, you can output the bad rows to a flat file or to another table so you can review the errors on a future date.
3. Fast load options are properties of the destination and not the source. You can use fast load even if you redirect error rows from your source to somewhere else. I just ran a performance test on a million row data set with the fast load ORDER option and the performance was basically identical when I added error redirection and redirected 500K rows to a Row Count transformation. I also verified performance was slower when I removed the fast load option, so I'm certain this has 0 impact.