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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.