I am having an issue where
SqlBulkCopy is not copying all of the data in a column over to the destination table. I have verified that the source data (which is a
.CSV file) has values in the column in all of the rows, but only the first 40 or so rows in that column are getting copied over.
The destination table's columns are set to
NVARCHAR(255) and all of them are allowed to be nullable.
Here is my function to do the bulk copy:
Private Sub loadDataFromCSV(ByVal pathToFile As String, ByVal connString As String, ByVal file As String, ByVal colCount As Integer) Dim fileLocation As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathToFile & ";Extended Properties='text;HDR=NO;FMT=Delimited(,)';" Dim qry As String = "select * from " & file Dim CompData As OleDbDataReader Using destConnection As SqlConnection = _ New SqlConnection(connString) destConnection.Open() Using sourceConnection As New OleDbConnection(fileLocation) Dim cmdSourceData As New OleDbCommand(qry, sourceConnection) sourceConnection.Open() CompData = cmdSourceData.ExecuteReader() Using bulkCopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(connString) bulkCopy.DestinationTableName = "dbo.Records" bulkCopy.BatchSize = 10000 bulkCopy.BulkCopyTimeout = 90 Try bulkCopy.WriteToServer(CompData) Catch ex As Exception Console.WriteLine(ex.Message) Finally CompData.Close() End Try End Using End Using End Using End Sub
As far as I can tell, all of the data from the table is making it over into the correct columns, with the exception of the 7th column. In the 7th column, I get the first 40 or so rows of data, and then the rest of the values for the column are NULL.
I've run out of ideas for what could be going wrong, so any help would be greatly appreciated.
My guess would be it is coversion errors. OLEDB will infer the datatype of a column based on the first 8 rows (I think) so if your first rows are:
SomeColumn ---------- 1 2 3 4 5 6 7 8 9 apple
This initially looks like an integer column, so this is what it is mapped to, but then when it gets to "apple" it can't convert it to an integer, so returns DbNull.
The solution to this is to add
IMEX=1 to your connection string, this means that no implicit conversion will be done, and the OleDbReader will just read exactly what is in the csv.
The downside of this is that you will probably then get conversion errors when trying to call the
SqlBulkCopy.WriteToServer(DataReader) method. You may need to create a DataTable in the same format as your database table, and iterate over your OleDbReader doing explicit conversions where necessary, then write this DataTable to the database with SqlBulkCopy.
As a hacky workaround I simply put a character string into each column in the top 8 rows of my csv file. It fools sqlbulkcopy into treating all fields as strings. Then in sql delete the records that contain the character string.