SSIS cannot convert between unicode and non-unicode

Question

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

Answer 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.