Search code examples
sql-servercsvencodingssisetl

SSIS: Code page goes back to 65001

In an SSIS package that I'm writing, I have a CSV file as a source. On the Connection Manager General page, it has 65001 as the Code page (I was testing something). Unicode is not checked.

The columns map to a SQL Server destination table with varchar (among others) columns.

There's an error at the destination: The column "columnname" cannot be processed because more than one code page (65001 and 1252) are specified for it.

My SQL columns have to be varchar, not nvarchar due to other applications that use it.

On the Connection Manager General page I then change the Code page to 1252 (ANSI - Latin I) and OK out, but when I open it again it's back to 65001. It doesn't make a difference if (just for test) I check Unicode or not.

As a note, all this started happening after the CSV file and the SQL table had columns added and removed (users, you know.) Before that, I had no issues whatsoever. Yes, I refreshed the OLE DB destination in the Advanced Editor.

This is SQL Server 2012 and whichever version of BIDS and SSIS come with it.

Solution

  • 65001 Code page = Unicode (UTF-8)

    Based on this Microsoft article (Flat File Connection Manager):

    Code page
    Specify the code page for non-Unicode text.

    Also

    You can configure the Flat File connection manager in the following ways:

    Specify the file, locale, and code page to use. The locale is used to interpret locale-sensitive data such as dates, and the code page is used to convert string data to Unicode.

    So when the flat file has a Unicode encoding:

    Then this property cannot be changed, it will always return to it original encoding.

    For more infor about the Code Page identifiers, you can refer to this article: