Is it possible to use System.Transactions.TransactionScope with SqlBulkCopy?

SqlBulkCopy never enlists into a transaction. SqlCommand also does not do that. Common misconception. The enlistment is performed at the time SqlConnection.Open is called. After that, anything that runs on that connection is part of the transaction implicitly. In fact it is no longer allowed to pass an explicit transaction.

If you want SqlBulkCopy to take part in a System.Transactions.Transaction using TransactionScope the transaction must be set at the time you open the connection.

It is very easy to do:

using (var tran = new TransactionScope(...))using (var conn = new SqlConnection(connStr)){  conn.Open(); //This enlists.  using (var sqlBulkCopy = new SqlBulkCopy(conn)) {    sqlBulkCopy.WriteToServer(...);  }  tran.Complete(); //Commit.}

This code is all you need. Possible mistakes:

1. The transaction must be opened early enough.
2. Do not use the SqlTransaction parameter of SqlBulkCopy. Pass null.
3. Do not use SqlBulkCopyOptions.UseInternalTransaction.
4. Do not add exception handling unless you want to actually do something. Rollback is automatic if there is no commit.
5. Use the using statement for clean code and deterministic cleanup. Do not manually close or dispose any of these objects unless you have to. This would be redundant.

You can use any batch size you like and all batches will be part of the transaction. Therefore, batching has limited value (in particular the transaction log cannot be truncated early). Try no batching at all first.

To perform atomic SqlBulkCopy imports that span across all batches (and, optionally, across other database statements) we need to use transactions. The following steps outline the process of using a transaction with SqlBulkCopy:

1. Create a SqlConnection to the destination database server.
2. Open the connection.
3. Create a SqlTransaction object.
4. Create the SqlBulkCopy object passing in the SqlTransaction objectinto the constructor.
5. Perform the import - the call to WriteToServer - within aTry...Catch block. If the operation completes, commit the transaction; if it fails, roll it back.

Using Transactions with SqlBulkCopy

The only way to define the transaction in a bulk load (to my knowledge) is to specify the batchsize.

The advantage of the bulk load is that you get a bulk update lock (multi-threaded read and a multi-threaded write). You get this when using bcp, bulk insert, a ssis data flow task with (tablock), a insert(columns)select columns from openrowset (bulk), or a sqlbulkcopy. This is handy when trying to minimize both the time to load and the transaction log size (only if you have satisfied the minimally logged requirements, which will save you hours on millions of rows).

Anytime you load data, the transaction log is going to be the bottleneck. If time is of the essence, it's important to minimize how much gets logged.

Once the batchsize is satisfied (the number of rows that you specified to commit on) the transaction gets committed and starts over. If you specify a batchsize of 0, the transaction will cover the entire file and rollback if any data issues arise.