The problem appears to be that the lines that are not importing correctly have characters that, encoded in code page 950, having a trailing byte of
0x7C, which is valid, but also happens to be the pipe symbol, which you are using as a delimiter. For example:
SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'四會'
-- 0xA57C B77C
Each of those two characters has a trail-byte of
0x7C. The parser seems to be viewing that as a delimiter instead of part of a 2-byte sequence for a code page 950 character. This is why you get a "?" in both "City" and "Country" with the remaining input line in the "Gender" column. The "?" in those two columns is due to the lead bytes
0xB7 not being valid on their own.
The next line has the same issue:
SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'元朗大坑|'
-- 0xA4B8 AED4 A46A A77C 7C
This time I kept the delimiter that comes at the end of the "City" value (so it would be clearer that the delimiter is the same byte value as the trail byte of that 4th character). The 4th character,
坑, is encoded as
0xA77C in code page 950. This is why only the 4th character shows up as "?".
So, this might be a bug in SSIS. Or, perhaps it's a configuration issue. Is there a way to indicate that the entire file, not just this one column, is code page 950? Dealing with code pages on a column-by-column basis, in terms of a text file, makes no sense. The entire file is encoded as code page 950, not just that one column. If it's possible to change the delimiter that might help, but it most likely just delays the problem as any delimiter can be encoded as a valid trail-byte value for double-byte characters. Given that
OPENROWSET works correctly, I have to believe that this also can (although you aren't setting the file's encoding for
OPENROWSET, just the column, so this could still be an SSIS bug).
Please try the following to set the file's encoding within SSIS:
- Go to the Flat File Connection Manager Editor (General Page)
- Under "Code Page", enter in 950.
- Make sure that "Unicode" is not selected.
Also, check the "CITY" column under "Output Columns" and make sure that it is using "Standard Parse" and not "Fast Parse" (since "Fast Parse" is locale-insensitive).