How can I get SqlBulkCopy to tell me which column had a truncation error

Question

I am using SqlBulkCopy to import from an external database. When importing into an nvarchar column, if the column is not large enough to hold the incoming row, it does not work:

InvalidOperationException: A specified value of type String from the data source cannot be converted to the nvarchar type of the specified target column. String or binary data will be truncated.

I would like to tell the user which target column arose. I combed the exception, but I do not see it anywhere. Is there a way to set the column name or index to be returned as an exception?

Here is the pseudocode of my bulk copy:

using (DbConnection source = DataTableProviderAssists.GetTypedDbConnection(package.ImportSourceType, package.UnencryptedConnectionString)) { using (DbCommand cmd = GetCommand(package, source)) { source.Open(); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(RequestContext.ConnectionString, SqlBulkCopyOptions.TableLock)) { bulkCopy.DestinationTableName = temporaryTableName; bulkCopy.BatchSize = 40000; bulkCopy.BulkCopyTimeout = 60000; foreach (ImportField field in package.Fields) { bulkCopy.ColumnMappings.Add(field.Name, field.Name); } bulkCopy.WriteToServer(reader); } } } 

thanks

Answer 1

I'm not sure what it is.
You may need to resolve this issue differently.
Try using the Microsoft.SqlServer.Management.Smo API to query the destination table and find out what is the maximum column length in your table.
After that, you can pre-read the source data and immediately eliminate the exceptions.
Enjoy.

Answer 2

I voted for the approved answer, as it helped me a bit.

I decided to use SSIS to investigate the problem, and although it took me 30 minutes to get it working, I now have a tool that I can reuse to solve the same problem in the future.