MaximumErrorCount has now effect

Question

Hi

I'm quite new to SSIS - using 2008 version.

I have a job that uses a few data flow tasks. On the third one I'm getting a primary key violation on the last row that it needs to insert, but only sometimes!

I'd like to ignore this problem for now and let the job continue. I have set the MaximumErrorCount property to 10 for the DataFlowTaks, the SequenceContainer and for the Package but still taks fails and this causes the package to stop.

Could anyone please advise how I can get the package to ignore the error?

Thanks

Rob.

Answer 1

That error count refers to the number of Tasks that SSIS will allow to error before it stops the package. You're wanting to allow a set number of rows to error - and that's not what it's counting.

Instead, you should go into your Destination and configure the Error Output on that destination to either ignore errors, or redirect errors (better). You can then pull a red arrow off the bottom of the destination component to a Derived Column (or any other type of component that doesn't need to attach its output to anything), and put a Data Viewer on that red link. Now all the rows that fail will go to the Derived Column, and show up in a Data Viewer for you to see (while in BIDS).

The other thing you'll have to do is change the Batch Size on the OLE DB Destination (if that's what you're using) to 1 so that it only inserts one row at a time. Otherwise, it will fail the whole batch that contains the error...