SQL Server Bulk Import With Format File of UTF-8 Data

Question

I have been referring to the following page:

http://msdn.microsoft.com/en-us/library/ms178129.aspx

I simply want to bulk import some data from a file that has Unicode characters. I have tried encoding the actual data file in UC-2, UTF-8, etc but nothing works. I have also modified the format file to use SQLNCHAR, but still it doesn't work and gives error:

Bulk load data conversion error (truncation) for row 1, column 1

I think it has to do with this statement from the above link:

For a format file to work with a Unicode character data file, all the input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).

What exactly does this mean? I thought this means every character string needs to be a fixed 2 bytes, which encoding the file in UCS-2 should handle???

Answer 1

This blog post was really helpful and solved my problem:

http://blogs.msdn.com/b/joaol/archive/2008/11/27/bulk-insert-using-unicode-data-files.aspx

Something else to note - a Java class was generating the data file. In order for the above solution to work, the data file needed to be encoded in UTF-16LE, which can be set in the constructor of OutputStreamWriter (for example).