Error handling with SqlBulkCopy - could it be any harder?

Question

Running very low on ideas here. I've got a case where I'm using SqlBulkCopy to pump data into a DB, and about halfway through I run into different exceptions (primary key violations, index violations, etc).

I've confirmed that the violations are in fact true and need to be corrected in the data. What's infuriating, though, is that if I were writing to the DB with a DataAdapter (which would be far slower), the bad rows in the DataSet would have HasErrors turned on so I could easily find them and take care of things. With SqlBulkCopy? Zilch. Nada. Good luck finding whoever caused your problem because all you'll get is an error name (like "primary key violation in yada yada yada, blah blah blah") and that's it.

Any suggestions? I can't believe there's no way to get these errors. With the standard BCP I think you can even pump these things to a log file. Can't we do something like this with SqlBulkCopy?

Thx,

Answer 1

Have a look at the source code for SqlBulkCopy - you'll get your answer there - there is no real error handling there.

One thing you could do, although lame that you would have to, is to run an erroring batch with the batch size set to 1.

Answer 2

When I'm doing data imports requiring validation, I usually will dump the data into a table that will take the data as is, then run a stored proc or some other sql that can validate my data in a set based manner, do the transformations, and put it into the final destination in a way that I can control.