Random behavior of SqlBulkCopy with DbDataReader

asp.net excel sqlbulkcopy sql-server

Question

I'm currently writing an Asp.Net site that will upload an Excel file and save the contents to a SQL Server database and using SqlBulkCopy and a DbDataReader to do it.

The columns of the Excel and the database tables are named exactly the same.

Relevant code (reduced number of column mappings for clarity)

' Connection String to Excel Workbook
Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)
Dim connection As New OleDbConnection()
connection.ConnectionString = excelConnectionString
Dim command As New OleDbCommand("select * from [Sheet1$]", connection)

' Bulk Copy to SQL Server 
Dim bulkInsert As New SqlBulkCopy(sqlConnectionString)
bulkInsert.DestinationTableName = "UploadedOrders"
bulkInsert.BulkCopyTimeout = 0
bulkInsert.BatchSize = 50
bulkInsert.ColumnMappings.Add("Id", "Id")
bulkInsert.ColumnMappings.Add("CustomerId", "CustomerId")
bulkInsert.ColumnMappings.Add("Net", "Net")
... rest of columns

connection.Open()
Dim dr As DbDataReader = command.ExecuteReader()
bulkInsert.WriteToServer(dr)

The last call of WriteToServer throws an error saying

Column 'Net' does not allow DBNull.Value.

which it doesn't.

However there are no Nulls in any of my test files. Some of them fail and some works. It complains about different columns for different files, but always the same for a specific file.

Here's what I have tried so far and conclusions from it:

  1. I split up one of my test files in smaller pieces in an attempt to identify data error. A smaller file works, so I added more and more rows from the original file until it broke, and it did. This would indicate data error. But the row looks fine and putting it in another file it works. And moving it to the second last and removing the (now) last row and it works. So not a data error.

  2. Next idea was size and when adding the rows one by one until it broke there was a repeatable behavior - adding the row and the file won't work. Removing it and it works. And the magic number was 13959 rows. But a few of the files that are working are bigger, so I tried another file that didn't work. It showed the exact same behavior, but it broke after 14259 rows. So not a size issue, although it does demonstrate size related problems. Repeatable, but different for different files. And - once again - other, bigger files work.

  3. Tried all kinds of timeouts and bulksizes of the SqlBulkCopy woth no luck.

  4. Tried all kinds of ADODB provider strings with excel 12, 8 with or without XML, HDR etc. No change.

  5. Tried three different packages of ACE drivers (I'm running 64 bit Windows 10 and Office 2010 64-bit, so there should be one correct, but I read it had some problems so I tried the Office 2007 and 32 bits also). No change.

  6. Added a code snipped to actually read through the DataReader row by row and look for rows with Net=Nothing in case it was a problem of reading the Excel rather than writing to the Database. Couldn't find any.

  7. Added a question here, hoping anyone can provide some further things to test or some insights to what's going on. Doesn't make sense to me....

Thanks

UPDATE:

Tried adding all column names in Excel select as suggested by Gordon (thanks!). Didn't work.

Also tried adding a "where xx is not null" for a couple of parameters that threw errors in different files as suggested by Bruce (thanks!). This makes the error go away and the file is uploaded. However only a part of the file is imported. For one file 14088 records were uploaded of 30727 in the file and for another file 14305 were uploaded of 81195 records.

Still no logic in number of records, still no apparent data errors or null values in Excel. It doesn't make sense...

And still not sure if the error occurs in the read from Excel or the insert into database.

Also just found this in the event log when doing an upload. No idea if it is related.

"A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 141460, committed (KB): 393412, memory utilization: 35%%."

Popular Answer

Ok, so I found the problem and the solution.

Turns out it wasn't related to the BulkCopy or the database. It was the read from Excel that faltered.

After some more debugging and temporary code I found that only some 14' records were read from Excel into the DataReader.

Added the IMEX=1 to the connection string and cleaned up some double+triple quotes and it works. Ace connection string is now:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path)

Thanks for all input. Greatly appreciated.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why