SSIS Oracle Character Sets

During a recent data migration from Oracle to SQL Server, I came accross the problem of trying to import Greek encoded strings into our SQL Server database. With the standard character set installed with my Oracle client, all I got was upside down question marks with the odd square and ampersand thrown in for good measure.

Amazon Link: Oracle PL/SQL Programming

After a little research I found that in order for the SSIS Oracle connection manager to recognise the Greek encoded characters on the source database, it is necessary to make a change to the registry on the SSIS host server (which needs to have the Oracle client tools installed). The registry key that needs to be updated is HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEid and in this instance the NLS_LANG parameter needed to be updated to GREEK_GREECE.EL8MSWIN1253

This page on the Oracle website explains a little more about the NLS_LANG environment variable.

And this table gives the possible values for use in Windows environment.

I’m not much of an expert on Oracle, and found it quite hard to get the info I needed for this issue, so hopefully these links should help.

Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.