We have a process that loads flat files into a staging table. The table has varchar(2000) columns, and the SSIS package is using code page 1252. We've noticed that some unicode characters are being converted into their multi-byte expressions in this staging table and it's breaking some data quality checks we're doing. It's taking the long dash (– Unicode decimal 8211) and converting it to â€“.
I am thinking this is because the varchar columns are ASCII and we're trying to shove unicode characters into it(?). I tried switching the code page to UTF-8 (65001) on the flat file source connection, and then I set the default code page on the destination db connection to 65001 and set AlwaysUseDefaultCodePage to True. I want the characters to display as they do in the flat file. In other words, a long dash needs to stay a long dash, and other oddball characters need to stay in their correct display form as well. How can I accomplish this?
The server/database/table/columns are using SQL_Latin1_General_CP1_CI_AS collation.
This stackexchange question seems to be similar, but it's not getting me where I need to go.