July 4, 2011 at 2:52 pm
Hello,
I have a SSIS package that gets Oracle information and then is placed to the local SQL 2008 R2 server.
At development phase I got the popular "cannot convert between unicode and non-unicode string data types." (damn you microsoft!!) so to avoid casting every field I went to the 'easier' task of changing the local tables from (var)char to n(var)char.
Now the package finally runs on the development workstation, but now when migrating to the production server (64b bit) I'm getting that funky unicode error again!
The oracle client at the server is 64 bit and SSIS is also running on 64 bit mode. That's basically the difference between the dev workstation and prod server.
Anyone knows what is the problem??? It would seem the 64-bit Ora client is not pushing unicode data, or that SSIS exec receives the information and declares the results as not unicode compliant.
July 5, 2011 at 7:49 am
project properties > Run64BitRuntime property ? maybe?
July 5, 2011 at 8:22 am
Set it to TRUE (the default value). That way would be supposed to work in the same way in the server as works on the workstation.
July 19, 2011 at 2:10 pm
I finally was able to figure out what was happening.
The 32-bit Ora Client sets up the default settings to UTF8 while the standard Ora 64-bit client sets it up with WE8MSWIN1252 settings. After changing the NLS_LANG registry key in the server to AMERICAN_AMERICA.UTF8 fixed the issue.
I clarify that the 32-bit client has been installed in each workstation image, I'm not certain if that's the default value in a vanilla client installation and may be a custom change made by my corporate office.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply