Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot convert between unicode and non-unicode string data types


Cannot convert between unicode and non-unicode string data types

Author
Message
csdunaway
csdunaway
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 235
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
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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
csdunaway
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 235
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
nagireddysdd
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 56
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
Bapaiah Kollipara
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 110
Consider Using .NET OracleClient Data Provider. That should work.......
csdunaway
csdunaway
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 235
Thanks, everyone. I got it working fine now. :-)



Phil Coons
Phil Coons
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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
csdunaway
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 235
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
chalamsbi
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
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
Bapaiah Kollipara
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 110
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search