can't add datatable to sqlbulkcopy

Question

This is my code

public static void InsertToDatabase(DataTable dataTable) {
 using (SqlBulkCopy sbc = new SqlBulkCopy(GetConnectionString())){
 sbc.DestinationTableName = tableName;
 sbc.WriteToServer(dataTable);
 }
}

This is the exception:

The given value of type String from the data source cannot be converted to type nchar of the specified target column.

Is there any way to know on which column the exception belongs?

• Rahul: How you are creating the datatable? define table column exact as target table.
• Agnieszka Polec: @Rahul it is a long story, I am using CsvHelper library to read data from about 500 csv file and load that data into a datatable. Anyway, that works fine and I don't think that the datatable has any mistake
• Agnieszka Polec: @Rahul yes sure, the table columns are exactly as the datatable columns. 100% sure of that. I am aware to this point
• Rahul: OK. In that case, there must be size mismatch. I mean the particular column in exception is having a size which is not able to hold the size of data in datatable. Can you check and confirm that.
• Agnieszka Polec: @Rahul I don't know if I understood you correctly, but the database table has 39 column and my csv files has 38. However the remaining 1 column is nullable . is that what you mean?

Answer 1

There must be size mismatch. I mean the particular column in exception is having a size which is not able to hold the size of data in datatable.

what I mean is if column in exception is defined @DB end as say nchar(10) and you are trying to insert a data of size > 10 chars then also you will hit this exception.

Check and make sure the same.