March 9, 2012 at 9:22 am
I am working on an SSIS project, to pull data from Oracle 10.2g into SQL Server 2008R2, 64-bit. This project was running fine on a 32-bit SQL 2008 database, but after moving it to the new 64-bit server, I am getting this error:
Code: 0xC02020F6
Description: Column "XXX" cannot convert between unicode and non-unicode string data types.
End Error Error: 2012-03-09 11:01:03.31
This package runs fine on the server inside Visual Studio, but when I put the dtsx file in the SQL Agent job, I get these errors all the time. I have added the Data Conversion to this, but it does not help with the error when run as a SQL Job. I have searched everywhere, and cannot find a fix.
Can someone help me?
March 9, 2012 at 9:44 am
I doubt it has anything to do with the 64-bit or running it in a SQL Agent job. There are definitely plenty of Unicode characters that won't convert to a non-unicode string. Try changing the error output for the conversion step to redirect the row out to a flat file rather than fail the component and have a look if there are any weird characters in it.
March 9, 2012 at 12:14 pm
Well, it does seem to be a 64-bit issue after all. I checked MSDN for this, and found this link
http://msdn.microsoft.com/en-us/library/ms141766.aspx
When I run the dtexec, I get the errors. When I run the dtexec from the 32-bit folder(C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn), it runs fine.
April 12, 2012 at 12:41 am
u should delete the source adpter and recreate again,it will work because it takes old buffer thats why it showing error like that
April 23, 2012 at 2:42 pm
Consider Using .NET OracleClient Data Provider. That should work.......
April 24, 2012 at 5:34 pm
Thanks, everyone. I got it working fine now. 🙂
July 26, 2012 at 12:38 pm
what did you do to get this working? I am having the same problem and I have tried all the above suggestions, with no luck. I am on a sql server 2008 r2, connecting to an oracle database 11g r2 to import from. trying to import to a sql server 2008 r2 database from oracle
July 26, 2012 at 12:44 pm
Phil Coons (7/26/2012)
what did you do to get this working? I am having the same problem and I have tried all the above suggestions, with no luck. I am on a sql server 2008 r2, connecting to an oracle database 11g r2 to import from. trying to import to a sql server 2008 r2 database from oracle
You may have to put a data conversion between your input and output connections. Sorry, I am not on my development computer to tell you the proper names...
August 14, 2012 at 1:46 pm
Hi, Could someone tell me how to resolve this issue.
I changed my oracle source database from 9i to 11g.
Everything worked fine earlier.
But I get the following error now
Code: 0xC02020F6 Source: Load Audit_Log ViewPoint xxxTable_Namexxx Source [1] Description: Column "xxxColumn_Namexxx" cannot convert between unicode and non-unicode string data types. End Error
***Followed by all columns***
I believe it is the 64bit issue.
I am running the job from SQL Server Agent.
August 14, 2012 at 4:27 pm
Hi , If you are getting the error when exporting Oracle data to SQL Server using SSIS package. You need to use Data conversion to Transform Data.
Using Data Conversion change the string Data Type from "Unicode String[DT_WSTR]" to "string[DT_STR]".
Below Link for Article may help: http://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/
July 11, 2013 at 9:11 am
Alright... I've beat my head against a wall on this issue at different times....
Let me just start with Yes you do have to have a data conversion task before loading into SQL Server. My specific situation is the source is an Oracle 11g server and the data is stored as varchar2. I have setup my sql server 2008 R2 table where the columns are nvarchar2 (unicode).
I have a data conversion task to convert the oracle columns to Unicode (WSTR_STR or something like that... I can't access my server to verify exactly how the converstion task references that).
The Oracle source is using the Oracle OLDB connector and the SQL Server Destination is using the SQL Server OLEDB connector.
I'm using the Oracle OLEDB connector because I have to build a select statement variable (to substitute in dynamic criteria).
All of this has been working fine until I promoted to a production project file. (ie a manual effort to of starting a new project file and loading it with the packages, etc of the currently working project and then updating the Data Sources to point to the production database (found on the same server)) .... getting the message unable to convert between unicode and non-unicode.
.... So in the new "production" project file, I changed the Oracle OLEDB connector to an Oracle.Net for OLEDB connector which required changes is how a built the dynamic query to be passed to Oracle..... This fixed the unable to convert unicode and non-unicode errors.
.... I came across this thread because of searching on the "unable to convert unicode and non-unicode" error. A couple of posts earlier than this one, someone mentioned that this maybe a 32-bit vs 64-bit issue.... I'm unable to test and confirm at this time; but, I bet that there is merit to that state has my Dev project is set to 32-bit and when creating the Prod project file, I forgot to set it to 32-bit (Solution Explorer->RightClick on Project->Properties->Debugging->Run64BitRuntime = False)
So... In short, if you're having problems with Unicode, Non-Unicode conversion and you are already using a Data Conversion task, the fix is either....
1. Use the Oracle.Net connector for your source
or
2. Set your project to run the 32bit Runtime.
June 16, 2017 at 1:16 pm
Hello, so my issue was similar in that I was trying to bring over Oracle 11g data into MS SQL. I have been connecting to other Oracle instances in this environment, but this was the first time connecting to this new source and was getting the message: Cannot convert between unicode and non-unicode string data types.
When I used this provider: OraOLEDB.Oracle.1 (AKA Oracle Provider for OLE DB), the normal Oracle varchar2 columns were being shown as DT_WSTR.
When I switched to use the Microsoft OLE DB provider for Oracle (MSDAORA.1), the source column stays as DT_STR so no issue between Unicode and Non-Unicode. Hope this helps others.
September 22, 2017 at 10:25 am
Bapaiah Kollipara - Monday, April 23, 2012 2:42 PMConsider Using .NET OracleClient Data Provider. That should work.......
It's works fine still with visual studio 2016 and sql server 2016
February 6, 2018 at 1:59 pm
Select the OLEDB Source and set ValidateExternalMetadata Property to "False". By default it is "True"
February 7, 2018 at 10:18 am
I would suggest that you alter the data type for your landing tables to be nvarchar. This should resolve the issue. You can change it after that as you stage the data. But the first thing you just want to do is OWN the data. Don't try to do things with it between the source and the landing table because you will add an unnecessary brittleness to the extract.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply