April 1, 2011 at 12:24 pm
I ahve about had it with SQL Server 2008 64 bit!!!! I am creating a SSIS package with a SQL query to Oracle and trying to put the file into a flat file. I am using the Oracle OLEDB source and a Flat File Destination for the output. Everything works fine locally, but when put on the server and run through SQL Agent I keep getting the Unicode to Non-unicode errors!!! The latest drivers are on the server and the 11g client is on my development machine. The types shown in each step show as DT-STR. I have the exact same source writing to an OLEDB destination just fine. I don't want to have to write these to a table and then pull them back out just to get this to work. Any solutions? And please, no "just add this" responses. I have tried a data conversion, but get same result. Please supply DETAILED answers as in go here and change this to this. Pictures never hurt. Thanks
April 1, 2011 at 12:51 pm
As we can't see from here what you see from there, a little hard it is to tell you exactly what you need to change.
April 1, 2011 at 1:06 pm
I have an OLEDB source using the 11g oracle provider. The data type in the metadata is set my SSIS as DT_STR. The output fields on the Flat File Destination/connection are all DT_STR and works fine in 32 bit mode in development. When moved to SS 2008 64 bit server and run, I get unicode conversion error. I was hoping that someone else has done the same thing, solved the issue and can tell me what THEY changed to get this to work.
April 2, 2011 at 1:56 am
I don't know why you are getting the Unicode conversion errors but have you tried setting the agent job to run in 32 bit mode? Is there any other difference between your dev machine and prod? Is the Oracle database they are connecting to identical?
April 3, 2011 at 9:32 pm
I saw this sort of issue a few weeks back...32 bit development then deployed to 64 bit server and error with unicode conversion. We had the DBA's install 32 bit Oracle drivers (sorry don't have all the details of products and versions) and the problem went away.
June 10, 2011 at 7:39 am
Not sure if this was solved or not, but I will post how I solved the issue.
error message: column "XXXXX" cannot convert between unicode and non-unicode string data types.
scenerio: I am using SSIS (BIDS 2008) running on Windows 64 bit and trying to retrieve data from an Oracle database. FYI - I have 32bit oracle drivers installed on my windows 64 bit box.
1. from the data flow task, I right click on my oracle oledb source and click "Show advanced editor".
2. go to the "Input and output properties" tab
3. drill down on the "OLE DB Source Output", then to "Output columns"
4. find the column causing the error and click it
5. Then on the right hand side of the page (Common Properties) you will see the "Data Type" property
6. change this to "string [DT_STR]"
7. click OK.
This procedure worked for me. Hopefully it helps someone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply