SqlBulkCopy catching errors and continuing execution RRS feed

  • Question

  • Hi there:

    I'm doing a data bulk copy using SqlBulkCopy class. Any time an error occurrs, the process stops. I'd like to somehow manage the errors (catching exceptions or checking for inserted rows state) but always letting the bulk copy operation to continue. I can't find a way to do this. Does anyone knows how?

    Thanks a lot,


    Wednesday, September 22, 2010 10:25 AM


  • I believe that SqlBulkCopy.WriteToServer is an atomic operation unless you change the BatchSize property so that it executes in batches. I'm not sure what would happen if you set the BatchSize to a value of 1 (row), but that is the only circumstance I can think of where a failure to update a row in a batch would not rollback other updates.

    The bottom line is that all updates in a batch must be successful and if one update fails then all updates applied to that point will be rolled back.

    Other options would be to simply update one row at a time or use the DataAdapter.Update method for a DataTable or DataSet (both are slower performance-wise).

    http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx (see the Note section)



    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alex Liang Wednesday, September 29, 2010 10:31 AM
    Thursday, September 23, 2010 12:09 PM