Sunday, August 29, 2010

SqlBulkCopy with SQLAzure

Problem

Using SqlBulkCopy (.NET 4.0) with SQL Azure (10.25.9386) gives the error:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The SqlBulkCopy class is recommended by Microsoft for migration in their General Guidelines and Limitations (SQL Azure Database) document. However, it is clear to me that this does not work having tried many approaches, generally of the form:
  
Using cn As SqlConnection = New SqlConnection("connection string")
cn.Open()
Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(cn)
BulkCopy.DestinationTableName = "table"
BulkCopy.BatchSize = 5000
BulkCopy.BulkCopyTimeout = 120
Try
BulkCopy.WriteToServer(ds.Tables(0))
Catch ex As Exception

End Try
End Using
End Using


Alternatives

  1. Use a traditional row at a time insert.
  2. Write a wrapper around BCP which works fine.

Labels: ,