SilverFish
SilverFish

Reputation: 1106

SQL Bulkcopy Error "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column"

I have a console application is ASP.NET/C#. The process copies a number of tables and therefore, I have a generic method to bulk copy. But there was a change in col size on the source and it started throwing the error. I have included ColumnMappings code in my method, but still error does not indicate which column is the error coming from. Please provide suggestions.

public static void BulkCopyNew(SqlConnection destinationConnection, string destinationTableName, DataTable dataTable, SqlBulkCopyColumnMapping[] columnMappings)
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
    {
        try
        {
            destinationConnection.Open();

            bulkCopy.DestinationTableName = destinationTableName;
            bulkCopy.BatchSize = 5000;
            bulkCopy.BulkCopyTimeout = 180; //seconds

            WriteToLogs("Bulk copied " + destinationTableName + "; Count: " + dataTable.Rows.Count);

            foreach (var mapping in columnMappings)
            {
                bulkCopy.ColumnMappings.Add(mapping);
            }

            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception ex)
        {
            WriteToLogs(ex.Message + "; Error bulk copy Data");

            Environment.Exit(0);
        }
        finally
        {
             dataTable.Dispose();
             destinationConnection.Close();
        }
    }
}

Upvotes: 0

Views: 261

Answers (0)

Related Questions