SqlBulkCopy with implicit truncation

Question

Trying to insert data using SqlBulkCopy, and I get this error:

Received an invalid column length from the bcp client for colid 6

They recommend one of two things:

Increase varchar size in DB (to accommodate larger strings)
Manually truncate the strings in DataTable (with my own code) before uploading (to make strings appropriate size)

But I'm happy for "implicit truncating" to occur... (i.e. the DB insertion or the SqlBulkCopy itself cuts the users' supplied value down to appropriate size)

Questions:

Is it possible to use SqlBulkCopy and get "implicit truncating" behavior like when setting SqlCommand "SET ANSI_WARNINGS OFF", so that "When OFF, data is truncated to the size of the column and the statement succeeds"
Can I change a setting on my DB to allow "implicit truncating"?
Is there an alternative to SqlBulkCopy which will allow this?
Or maybe I should just use one of the recommended approaches (but I feel like manually truncating the data in the DataTable on my own would have a performance penalty?)

There is no way of automatically truncating the columns. If you've measured the time taken to truncate the columns and determined that the time taken is too long for your requirements then you could [use a staging table](http://stackoverflow.com/questions/6400635/how-to-automatically-truncate-string-when-do-bulk-insert) — stuartd, Nov 05 '14 at 01:53
Why dont you write a query for the copy, and for colid 6 use a substring call to force the data to the correct size — John Bingham, Nov 05 '14 at 02:28
@stuartd; good points, I haven't measured the time to truncate myself. And in this case I am loading data into a staging table; I could just make the varchars large. @John Bingham , can you elaborate on "query for the copy"? Would it use BULK INSERT? Is it competitive with SqlBulkCopy performance? And if it were a query, couldn't I just use "implicit truncating" by typing `SET ANSI_WARNINGS OFF`? — Nate Anderson, Nov 05 '14 at 03:43

score 5 · Accepted Answer · answered Jan 28 '21 at 18:32

I realize this is a very old post but having just run into the issue and not finding much help out there I wanted to share the solution I found. You can turn off ANSI_WARNINGS on the connection you pass in when creating the SqlBulkCopy object and SQL Server will silently truncate the data for you rather than throwing an exception.

using (var bulkConn = new SqlConnection(destConnString))
{
    bulkConn.Open();
    using (var cmd = bulkConn.CreateCommand())
    {
        // kill the data truncation errors
        cmd.CommandText = "SET ANSI_WARNINGS OFF"; 
        cmd.ExecuteNonQuery();
    }
    using (var writer = new SqlBulkCopy(bulkConn, BulkCopyOptions, null)
               {
                   BatchSize = 10000,
                   DestinationTableName = $"[{schema}].[{table}]",
                   EnableStreaming = true,
                   BulkCopyTimeout = 0
               })
    {
        writer.WriteToServer(data);
    }
}

SqlBulkCopy with implicit truncation

1 Answers1