I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).
Here is a sample file like one that I need to import: https://www.pastiebin.com/5d7782d9b63fa
Table where file has to be imported into has such a structure:
create table dbo.Test (
AccountId numeric(18, 0) not null
, Province nvarchar(50) null
, City nvarchar(50) collate Chinese_Hong_Kong_Stroke_90_CI_AI
, Country nvarchar(50) null
, Gender nvarchar(50) null
)
If I import using OPENROWSET/BULK
then all the data is transferred correctly:
select AccountId, Province, City, Country, Gender
from openrowset (
bulk 'C:\chinese_sample.dat'
, firstrow = 1
, formatfile = 'C:\chinese_sample.xml'
) t
Here is a format file that I use: https://www.pastiebin.com/5d7783396f9e4
But if I try to import file using SSIS, then Chinese characters are not parsed correctly.
In Flat File Source I use DataType string [DT_STR] and CodePage 950. Then convert it to Unicode string [DT_WSTR].
Here is how text is imported into table:
As we can see some characters are parsed correctly and some are not. What am I missing?