I had a scenario wherein special characters (ü) from text file were not imported properly into SQL Database.
Fix is to change the code page from default one (1252) to 65001. New challenge is that destination is not compatible with 65001 code page. Change the code page back to 1252 using derived columns.
Following is checklist for making the change.
• Change the Code page from 1252 to 65001 on Flatfile Connection Manager.
• Verify that the codepage is changed in input and output columns of Flatfile Connection
• If there are any Text Fields, use Data Conversion to change the data type to DT_STR or DT_WSTR. This change is required to change the Codepage back to 1252;
• User Derived Column to change the Codepage of all columns (Example: (DT_STR,1000,1252)name). Now these new columns can be used as-is for destination.