12

This is my code with the following columns and in the DB, those columns are nvarchars.

SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, 
System.Data.SqlClient.SqlBulkCopyOptions.Default, transaction);

bulkCopy.DestinationTableName = "Test";
bulkCopy.ColumnMappings.Add("Number", "Code");
bulkCopy.ColumnMappings.Add("Type", "Type");
bulkCopy.ColumnMappings.Add("Group", "Group");
bulkCopy.ColumnMappings.Add("Short Text", "ShortText");
bulkCopy.ColumnMappings.Add("Text", "Description");
bulkCopy.WriteToServer(dataTable);

I am trying to insert a whole data table in a DB, using bulk copy but I am getting this error:

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

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
Rocshy
  • 3,391
  • 11
  • 39
  • 56
  • You'll need to show us some table structures, code and so forth - just with that error, we cannot possibly help you ..... - see http://tinyurl.com/so-hints – marc_s Feb 14 '12 at 07:35
  • please check you source data. There might be invalid data in the source. – Pongsathon.keng Feb 14 '12 at 07:43

2 Answers2

19

It may be caused by a too-short-column. If your ShortText column is defined as NVARCHAR(30) and you have a 40 character string, you may get that error. See here for example: http://forums.asp.net/p/1228890/2212376.aspx

zmbq
  • 38,013
  • 14
  • 101
  • 171
4

I have another answer for this question, maybe it can come in handy for someone. If you have a String which contains " (quotation mark), you get this error message too. At least I just got this error message, and I searched for the wrong row, and I found this. As I removed the quotation marks, the error disappeared.

KatieAmber
  • 77
  • 12
  • I spent hours trying to resolve this error and this turned out to be the culprit for me too. I just copied all of the data from my source database into an Excel spreadsheet, searched the sheet for the bad character and found one instance. – Krondorian Jan 06 '15 at 21:55
  • Is there a way to make it work with quotation marks, or insert them back after import somehow? – Andriy Kozachuk Nov 04 '16 at 14:12