Find Failed row/rows on SqlBulkCopy.WriteToServer exception and retrySqlBulkCopy by omitting those rows that failed

Question

I am trying to do a bulk insert of a List of object (List invoices). Sometimes it fails and throws an exception. However, I want to know which rows failed. This way I can redo the bulk insert by omitting those rows. Can I do this?

Answer 1

This is one of the draw backs to using Bulk operations, the feedback is an all or none kind of response.

When we use Bulk operations we are generally bypassing the validation and verification that would normally take place for each individual row, that is the main trade-off to improve performance. You can't have both.

SqlBulkCopy is deliberately designed for use with sanitised data

So you should first consider how to sanitise your data before you try to copy it, this can take many forms so we can't cover everything in this post.

The most common Constraints that can fail is with null values (in fields that do not support nulls) and foreign keys (either null or not matching). Usually we can pre-validate the bulk data for nulls and keys that do not yet exist, just query your bulk set to find the rows that have null values in columns that do not support nulls. You can also query for any rows where the values in foreign key columns do not yet exist in the target data base.

You have not provided any detail on the structure of your data that you have in memory, so this is an abstract example for filtering based on nulls:

DataTable bulkData;
... load the data
// Columns that do not support nulls: Col1, Col3
DataRow[] dataWithNulls = bulkData.Select("[Col1] IS NULL OR [Col3] IS NULL");
// Get only rows that do not have nulls
DataRow[] bulkDataSanitised = bulkData.Select("[Col1] IS NOT NULL AND [Col3] IS NOT NULL");

To do a similar query on missing FKs you must first get a distinct list of the FK values, then query the DB to find those values that do not have a match, then you can filter out the rows that have those missing FK values.

If you are approaching this from a generic point of view, so you do not know the table schema in advance then the conceptual process that we usually follow in this scenario is to break the bulk set down into smaller chunks and execute those chunks.

In your interface, allow the user to specify the start row and the number of rows to copy, if it works, remove the rows from the source set. If it fails, ask the user to try again.

---

Your last option is to not do this in bulk at all! You can still use SqlBulkCopy however only send one row at a time, this allows you to handle wach row when it fails.

If you were using SqlBulkCopy for performance reasons, (there are of course other non-performance reasons to use SqlBulkCopy) then all that performance is lost if you use this method, however if failure has a low frequency then first trying the full bulk operation, then on failure doing it row by row is an option.

This article on Code Project Retrieving failed records after an SqlBulkCopy exception explains a solution to assist this but it should be pretty easy for you to come up with your own implementation.

---

You could combine the two approaches, trying the whole lot first, then on failure splitting the table into a number of sub tables on failure, then continue to recursively try and then split the tables until you reach tables of 1 row. This would be similar to how the user could go through the same process of elimiation manually and would still retain some performance benefits over going row by row from the start, but this is only advisable for large sets that have relatively low failure rates.