'SSIS cannot convert between unicode and non-unicode

I've an SSIS package which works perfectly on various server but doesn't on 2 newly added server. It uses OLEDB (64bit, I've certified that) to get data from an ORACLE DB from a table with 3 VARCHAR2 columns and upsert the rows to a SQL 2012 table with 3 nvarchar columns, with the same size.

The package was working flawlessly in all the servers we deployed it. We recently added 2 new servers, with hopefully (I personally checked the possible major culprit: ODAC, dtexec version, tns names ) the same software with the same versions but the package is not working anymore. It gives us the famous error: 

   Description: Column "C_CODE" cannot convert between unicode and non-unicode s
tring data types.
End Error
Error: 2016-08-26 08:33:26.20
   Code: 0xC02020F6
   Source: Upsert Lines OLE DB Source [87]
   Description: Column "S_DESC" cannot convert between unicode and non-unicode s
tring data types.
End Error
Warning: 2016-08-26 08:33:26.20
   Code: 0x800470C8
   Source: Upsert Lines OLE DB Source [87]
   Description: The external columns for OLE DB Source are out of synchronizatio
n with the data source columns. The external column "C_CODE" needs to be updated
.
The external column "S_DESC" needs to be updated.
End Warning

The first warning I got was the "Cannot read the code page from Oracle server" so I tried to put the "use the default one every time" in combination with ANSI or UTF-8 code pages ( used this website to get the codepages: https://msdn.microsoft.com/en-us/library/windows/desktop/dd317756%28v=vs.85%29.aspx ).

The only way I made it work was to manually modify the OLE DB Source block into the package by changing the external columns to ansi strings, making it not compilable in the dev environment, then put a data transform that would change the exernal columns to utf-8 string again. This way it doesn't work in the other servers but does flawlessly in the "faulty" servers.

I was thinking that the fact the package cannot read the code page from Oracle server is the culprit but I couldn't figure out how to fix it.

The problem is not like the million of questions about this issue you can find anywhere. The error being given is from the OLE DB Source block. I know about the data conversion.

Thanks in advance

sql-serveroracleunicodessis


Solution 1:[1]

The issue was the NLS_LANG registry key's value, it was set to an incorrect value. I solved by deleting the said key, all worked fine.

Solution 2:[2]

Just come across this post (and many others about this issue) and believe I have found the underlying cause. On my servers at least, the 64bit version of the Windows Oracle drivers has a bug in the install process. I have checked the 32bit version and this bug is not there which explains the behaviour.

It appears that the 64bit version creates the NLS_LANG registry key in the wrong location. By default the 64bit driver create it here - "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE"

I manually created it here - "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient12Home1" which cured my issue. For reference my NLS_LANG key is set to "ENGLISH_UNITED KINGDOM.WE8MSWIN1252". I just created it as a copy of the string in the branch above.

I hope this helps others.

Solution 3:[3]

We use 32 bit SSIS with Oracle instant client, I need to add entry in this registration location

"Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE\KEY_OraClient12Home1_32bit"

The “NLS_LANG” value is “AMERICAN_AMERICA.WE8MSWIN1252”. This solved my problem.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Massimo
Solution 2 JCPrints
Solution 3 Hoargarth

Related Questions