tracing fault with SqlBulkCopy in .Net

Question

I'm getting an exception using the SqlBulkCopy method to write a DataTable to a database table. Is there a way to get more specific information e.g. the column/value in error that is at fault because the error, as below, is not telling me much?

Input string was not in a correct format.
Failed to convert parameter value from a String to a Int32.
The given value of type String from the data source cannot be converted to type int of the specified target column.

I have checked and double checked and every data type in my datatable is the same as my database table and trying a test sample all is well. Just when I run it on a large scale it goes wrong. All entries are populated with valid data when I view the DataTable though.

My code is quite simple:

Dim trans As SqlTransaction = conn.BeginTransaction()
Try
     Dim c As SqlBulkCopy = New SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans) SqlBulkCopyOptions.TableLock, trans)
     c.DestinationTableName = $"CableLink"
     c.WriteToServer(t)
     trans.Commit()
Catch ex As Exception
     trans.Rollback()
     MsgBox(ex.Message)
End Try

My data table 't' is created from a List of a specific Class using reflection and this is where I checked every column type matches my class. The only real difference is some table columns are Integer mapped to smallint and Single mapped to float, but those aren't strings and the numbers fit the range.

The full stack trace is as follows

at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
   at PrepareCuReFiles.CableLinkList.SaveDataToSQLServer(String server, String db, String schema) in D:\DATA\Projects\CableLinkInfo.vb:line 519

Can you post or pastebin some data that reproduces this? – Caius Jard Jun 12 '20 at 17:41 — Caius Jard, Jun 12 '20 at 17:41

score 0 · Answer 1 · answered Jun 12 '20 at 14:25

0

Please check order of column in your database and data table should be matching as WriteToServer method does not map column names.

answered Jun 12 '20 at 14:25

manthan

102
1
15

Yes, because as mentioned in my original message, it works fine with a smaller sample. But I have checked anyway and it matches fine. – Neil Jun 12 '20 at 14:57
If that is the case issue might be with data itself. Instead of using WriteToServer iterate, log and insert all the records, it may be time consuming for you but probably you will be able to figure out which record is causing issue. Sorry for responding late. – manthan Jun 14 '20 at 08:11

score 0 · Answer 2 · answered Jun 12 '20 at 16:35

I followed this solutions, but while it didn't solve my solution (there were no faults raised by sql)

SqlBulkCopy with ObjectReader - Failed to convert parameter value from a String to a Int32

It dumped out the contents of a row to the debug log, so I fed that data in to a test object and it showed the fault to be in my class was a Single property but the column was an int.

So I guess, somewhere along the line it converted Single to String and then tried to go from String to Int. Maybe that's how type conversion works when trying to convert Single to Int32?

How did it show this? So you can make a complete minimum reproduction using this one line of debug data and maybe 10 lines of vb code? Add it to your question... — Caius Jard, Jun 12 '20 at 17:44

tracing fault with SqlBulkCopy in .Net

2 Answers2