SSIS - Flat file always ANSI never UTF-8 encoded

utf-8 ssis flat-file
Neil picture Neil · Mar 16, 2011 · Viewed 58.3k times · Source

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

Mirav Rathod picture Mirav Rathod · May 16, 2012

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.

Related questions

Why doesn't SSIS recognize line feed {LF} row delimiter while importing UTF-8 flat file?

I am trying to import data from a utf-8 encoded flat file into SQL Server 2008 using SSIS. This is what the end of the row data looks like in Notepad++: I have a couple more images showing what the file …

Read More
Encode String to UTF-8

I have a String with a "ñ" character and I have some problems with it. I need to encode this String to UTF-8 encoding. I have tried it by this way, but it doesn't work: byte ptext[] = myString.getBytes(); String value = …

Read More
Working with UTF-8 encoding in Python source

Consider: $ cat bla.py u = unicode('d…') s = u.encode('utf-8') print s $ python bla.py File "bla.py", line 1 SyntaxError: Non-ASCII character '\xe2' in file bla.py on line 1, but no encoding declared; see http://…

Read More