Search code examples
sql-serverssisetlflat-filecodepages

Converting flat file 8859-1 encoding to OLE DB Destination using codepage 1252

Input flat file is encoded with ISO 8859-1.

The database has default codepage 1252.

Is the only way to change codepages to use a Data Conversion transformation?

Yes, I know that 8859-1 and 1252 are very close, but I am trying to do this formally correct.

I must be missing something. It appears that I must use a Data Conversion transform, click on each and every field, and choose the codepage, then deal with the "Copy of " field names one by one. This does not seem believable.

Solution

  • When reading data from a flat-file there are different methods to convert the code page:

    1- Saving the flat file with a different code page:

    The easiest way is to open the flat file using a text editor and save it using another encoding. You may be able to do this using notepad (not sure if 1252 is supported) as an example:

    2- Trying to change it from Flat File Connection Manager

    Try to change the code page from the flat file connection manager, if implicit conversion is supported between code pages it may not affect the text.

    3- Using a Derived column Transformation

    You can add a derived column with the following expression:

    (DT_STR,50,1252)[InputColumn]

    4- Using a Data Conversion Transformation

    As you mentioned you can use a data conversion transformation to convert the code page.

    5- Use a staging table

    You can import data to a table having Nvarchar columns, then use an SQL command to insert rows into the destination table (having varchar columns)