utf 8 - SSIS - Flat file always ANSI never UTF-8 encoded

Question

Have a pretty straight forward SSIS package:

• OLE DB Source to get data via a view, (all string columns in db table nvarchar or nchar).
• Derived Column to format existing date and add it on to the dataset, (data type DT_WSTR).
• Multicast task to split the dataset between:
  • OLE DB Command to update rows as "processed".
  • Flat file destination - the connection manager of which is set to Code Page 65001 UTF-8 and Unicode is unchecked. All string columns map to DT_WSTR.

Everytime I run this package an open the flat file in Notepad++ its ANSI, never UTF-8. If I check the Unicode option, the file is UCS-2 Little Endian.

Am I doing something wrong - how can I get the flat file to be UTF-8 encoded?

Thanks

Answer 1

In Source -> Advance Editor -> Component Properties -> Set Default Code Page to 65001 AlwaysUseDefaultCodePage to True

Then Source->Advance Editor -> Input And OutPut Properties Check Each Column in External Columns and OutPut Columns and set CodePage to 65001 wherever possible.

That's it.

By the way Excel can not define data inside the file to be UTF - 8. Excel is just a file handler. You can create csv files using notepad also. as long as you fill the csv file with UTF-8 you should be fine.