SqlBulkCopy is not copying all the data in a column over

Question

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.

Thanks.

Answer 1

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.