Cannot convert between unicode and non-unicode string data types

  • csdunaway

    SSC Eights!

    Points: 856

    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?

  • HowardW

    One Orange Chip

    Points: 29049

    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.

  • csdunaway

    SSC Eights!

    Points: 856

    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.

  • nagireddysdd

    SSC Veteran

    Points: 227

    u should delete the source adpter and recreate again,it will work because it takes old buffer thats why it showing error like that

  • Bapaiah Kollipara

    SSC Veteran

    Points: 219

    Consider Using .NET OracleClient Data Provider. That should work.......

  • csdunaway

    SSC Eights!

    Points: 856

    Thanks, everyone. I got it working fine now. 🙂

  • Phil Coons

    Valued Member

    Points: 69

    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

  • csdunaway

    SSC Eights!

    Points: 856

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

  • chalamsbi

    Mr or Mrs. 500

    Points: 559

    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.

  • Bapaiah Kollipara

    SSC Veteran

    Points: 219

    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/

  • hise0001

    Valued Member

    Points: 55

    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.

  • rtagle 63214

    Grasshopper

    Points: 23

    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.

  • Alvaro Ortiz Book

    Grasshopper

    Points: 19

    Bapaiah Kollipara - Monday, April 23, 2012 2:42 PM

    Consider Using .NET OracleClient Data Provider. That should work.......

    It's works fine still with visual studio 2016 and sql server 2016

  • suppiunna

    SSC Veteran

    Points: 215

    Select the OLEDB Source and set ValidateExternalMetadata Property to "False". By default it is "True"

  • RonKyle

    SSC-Dedicated

    Points: 31423

    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 16 total)

You must be logged in to reply to this topic. Login to reply