cannot convert between unicode and non-unicode

Question

cannot convert between unicode and non-unicode

rui_fro

SSC-Addicted

Points: 467
More actions
May 2, 2008 at 3:20 am

#380302

Hi there!
I'm making lookups to SQL Server 2005 tables and inserting its values in another SQL Server 2005 table but I'm getting this error:"cannot convert between unicode and non-unicode". I've tried to make a data conversion but the problem continues.
Thanks for any help
😀

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

It is just what it says - in one of your components you are trying to insert, update, or join by a unicode field to a non-unicode field (or the other way around). Use CAST, CONVERT, a Derived column, or a Data Conversion component to make your data types the same.

elegantkvc SSCommitted Points: 1910 More actions · Answer 2

hi,

You have to use Unicode String[Dt_WStr] datatype for solve this error 🙂

cheers

vijay

johnkmichaud2 SSC Enthusiast Points: 124 More actions · Answer 3

Hi,

I am brand new to SSIS, and data types. Can you provide direction on this simple test i am doing with a DERIVED COLUMN?

(1) SQL Server 2005 OLE DB source: 2 columns, Address & City, both nvarchar(50) in db.

(2) Derived Column: expression (DT_TEXT,1252)City + (DT_TEXT,1252)AddressLine1. DATA TYPE: text stream[DT_TEXT]

(3) SQL Server 2005 OLE DB Dest: 1 column, Address, nvarchar(max) in db

ERROR: cannot convert between unicode and non-unicode string data types

I DON'T GET ERROR: if expression (DT_TEXT,1252)City

I assume this is a very simple error by someone who doesn't know what they are doing. Would a kind person point out the errors of my ways? Thanks.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Your database field is NVARCHAR(MAX) - this is UNICODE.

Your derived column is [DT_TEXT] - this is not UNICODE.

Either make the derived column [DT_NTEXT] or make the database field VARCHAR(MAX).

johnkmichaud2 SSC Enthusiast Points: 124 More actions · Answer 5

Thank you for that quick response.

But why don't i get the error when my EXPRESISION is "(DT_TEXT,1252)City",

and I do get the error when it is "(DT_TEXT,1252)City + (DT_TEXT,1252)Address".

padmapatibandla-858284 SSC Enthusiast Points: 103 More actions · Answer 6

Hello

I am trying to load data from sql to excel file.

I used data conversion task to convrt data.

i still get the error cannot convert between unicode and non-unicode datata types.

i converted varchar to unicode string[DT_WSTR].

can u help me

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Hi All,

Anyone have solution for this?

I am migrating data from SQL Server 2005 to Oracle 10G. I was able to load all large data sets but when it comes to Char fields, I get this error.

Error at Data Flow Task [OLE DB Destination [1797]]: Column "%columns%" cannot convert between unicode and non-unicode string data types.

My source is SQL Server table which has three fields of data type varchar and in oracle too I am chosing this data type as varchar2 but I am stuck with this error.

Could anyone have insight into this please?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Got this issue fixed eventually.

Added a Data Conversion component before loading the data into the OLEDB Destination.

My source columns were Non-Uni(DT_STR) while my OLEDB destination was expecting a output Uni(DT_WSTR). That's where the trick was. I have added a data conversion component to convert the source (DT_STR) to DT_WSTR and in the OLEDB destination component mapped the columns to that of derived columns from the data conversion component, which resolved the issue.

And Boom... all the SQL Server data is in the Oracle in couple of minutes....including the large data sets.

Before this we have tried to use SQL Loader/SQL Developer tool from Oracle, but unfortunately we had some issues with the Larger data sets and get some weird errors which the tool rejects them as bad rows. Once, again SSIS proved to be Superior!!!!!

tirosh.ziv SSC Journeyman Points: 86 More actions · Answer 9

get to the registry to configuration of the client and change the LANG.

for oracle go to HLM\SOFTWARE\ORACLE\KEY_ORACLIENT...HOME\NLS_LANG and change to appropriate language

Vikash Kumar Singh SSC Eights! Points: 974 More actions · Answer 10

Anyone looking for a solution there are multiple ways to resolve this issue.

SSIS: 3 Ways to resolve Unicode and non-unicode data type issue?

Vikash Kumar Singh || www.singhvikash.in