Error handling with SqlBulkCopy - could it be any harder?

dataadapter dataset exception sqlbulkcopy

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,

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why