Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cannot convert between unicode and non-unicode string data types Expand / Collapse
Author
Message
Posted Friday, March 9, 2012 9:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:28 AM
Points: 50, Visits: 178
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?



Post #1264475
Posted Friday, March 9, 2012 9:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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.
Post #1264488
Posted Friday, March 9, 2012 12:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:28 AM
Points: 50, Visits: 178
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.



Post #1264570
Posted Thursday, April 12, 2012 12:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:30 AM
Points: 121, Visits: 41
u should delete the source adpter and recreate again,it will work because it takes old buffer thats why it showing error like that
Post #1282162
Posted Monday, April 23, 2012 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 6, 2012 3:13 PM
Points: 3, Visits: 110
Consider Using .NET OracleClient Data Provider. That should work.......
Post #1288527
Posted Tuesday, April 24, 2012 5:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:28 AM
Points: 50, Visits: 178
Thanks, everyone. I got it working fine now.


Post #1289452
Posted Thursday, July 26, 2012 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 12:54 PM
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


Post #1336055
Posted Thursday, July 26, 2012 12:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:28 AM
Points: 50, Visits: 178
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...



Post #1336061
Posted Tuesday, August 14, 2012 1:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
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.
Post #1345000
Posted Tuesday, August 14, 2012 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 6, 2012 3:13 PM
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/
Post #1345080
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse