I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.
However, when I run a
SELECT * FROM tableName
the data appears mangled. For some reason it is not being stored in Unicode. The column is nvarchar, so that should not be an issue.
Also, if I insert data manually, the data is stored as expected.
What could be going wrong?
The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".
And to get your columns to DT_WSTR as liam.confrey mentions, you will want to click on your flat file connection manager and redefine each column type from string [DT_STR] to Unicode string [DT_WSTR]
Do note that if you misclick like I usually manage to do and select something like 'two-byte unsigned integer [DTUI2]', classic off by one, the OutputColumnWidth property will default to 50 once you reset to unicode strings. You will want to make the output width align with the input width (unless you have a reason for making it larger).
Do not try and mangle the flat file source in your data flow in the advanced editor settings to make things Unicode.
