SQLBulkCopy : Received an invalid column length from the bcp client for colid 10

SQLBulkCopy: Received an invalid column length from the bcp client for colid 10.

I recently encountered this exception while migrating data from old system to new system. I used SQLBulkCopy ddl to bulk copy of data and here is C# Code

C#:

void BulkInsert<TEntity> (IEnumerable items, string schemaName, bool keepIdentity = true) where TEntity : class

        {

            var context = new DBEntities() { AuditChanges = false };

            context.Database.Connection.Open();

            var conn = context.Database.Connection;

            var options = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints;

            if (!keepIdentity)

            {

                options = SqlBulkCopyOptions.CheckConstraints;

            }

            using (var scope = conn.BeginTransaction(IsolationLevel.ReadCommitted))

            {

                var copy = new SqlBulkCopy(conn as SqlConnection, options, scope as SqlTransaction)

                {

                 

                    BatchSize = 20000,               

                    DestinationTableName = schemaName + $".{typeof(TEntity).Name}",

                };

                var dbReader = items.AsDataReader();              

                copy.WriteToServer(dbReader);

                scope.Commit();

            }

         }

I looked into data and destination table schema and found that there are few record in source table, which length is exceeded to destination table's column.

So to resolve this issue, I simple increased the length of destination table column from varchar (10) to varchar (20).

Below SQL Script increase size of varchar column 10 to 20

         

 ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] Varchar(20)

Thanks for visiting!!