Question
I don't know why data isn't getting into SQL. Can someone please review this and see what's wrong with my code? I got most of the code below from and MSDN page: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx
Dim connectionString As String = "Server= "<servername>"; integrated security=true"
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
Dim commandSourceData As SqlCommand = New SqlCommand(<TSQL>), sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
Using destinationConnection As SqlConnection = _
New SqlConnection(connectionString)
destinationConnection.Open()
Using bulkcopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkcopy.DestinationTableName = _
"<tableName>"
Try
bulkcopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
reader.Close()
End Try
End Using
End Using
sourceConnection.Close()
End Using
Accepted Answer
In the system where I do a BulkCopy, I set up the column mappings as explained in the documentation Column Mapping - SQL Bulk Copy
The mapping is set up with the source and destination column names. This example is from the documentation:
Dim mapID As New _
SqlBulkCopyColumnMapping("ProductID", "ProdID")
bulkCopy.ColumnMappings.Add(mapID)
When I first set this up, I remember having trouble without explicitly setting up the column mappings in my environment.
Popular Answer
If the field-types, field-orders, fields count of your datasource, are not the same with the destination table. Your bulkcopy will return fail.
In above case, you should specificate the ColumnMappings of each fields in your datasoruce to your sqlbulkcopy object.
SqlBulkCopy copy = new SqlBulkCopy(MySqlConn);
copy.BulkCopyTimeout = 6000;
copy.DestinationTableName = TableName;
for (int i = 0; i < this.lstBulkFields.Count; i++) {
// if the source fields name are the same with the targets.
copy.ColumnMappings.Add(this.lstBulkFields[i], this.lstBulkFields[i]);
}
copy.BatchSize = BatchSize;
copy.WriteToServer(MyDataSource);