Cannot convert between unicode and non unicode string data type

Question

Good Morning,

I have a simple sql query that gets the data from an ORACLE db and loads into the SQL Server db 2005. When i'm trying to validate the package i'm getting an error which says "Cannot convert between unicode and non unicode string datatype".

Solution: I used the data conversion transformation and i used the alias columns which are by default named as "copy_filed name". I changes the data types from DT_WSTR to DT_STR, but am still getting the same error message. Is there something that i'm doing wrong?

Thanks

---

SV

Answer 1

Are you connecting the right columns in the destination?

Use a cast in the oracle source query:
http://www.techonthenet.com/oracle/functions/cast.php

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Hey Sai,

The SSIS data source taskl only looks at the Meta data of the external columns, first check the Columns tab in your source task, ensure that the External columns and the Output columns are of the same type.

You can work over the error by setting the ValidateExternalMetadata flag to false and then execute the package.

Another thing that you can try is implement CAST(COL as NVARCHAR2(32)) ALIAS in your SOurce SQL, this would ensure that the statements would always cast it as unicode and bring it in.

---

Regards, Dinesh

Answer 3

Thanks but no luck.

---

SV

Answer 4

Thanks Dinesh, they are of the same datatype DT_STR. I right clikced on the OLEDB source and compred the EXTERNAL Vs OUTPUT Columns on the OLEDB Source and all of them are having the same data types. Also i used the CAST(COL as NVARCHAR2(32)) in the source SQL Command, but still no luck.

Thanks

---

SV

Answer 5

In the Data Conversion component give a different Output Alias. So if your source column is "X" give the Output Alias as say "CNV_X" instead of specifying the same name as the source column. Now map CNV_X to the destination non unicode column and it should work fine.

---

~V

Answer 6

ok can you also check the following properties of the SourceTask - the validate meta data flag should be false, also set Alwys use default code page property to true.

If the cast did not work, i would recommend you to check the character set for the Oracle Source more often than not the column type would be defined as nvarchar2, but the collation would make the data types a non-unicode string. which seems to be your case as the external column is listed DT_STR.

Does your SQL DB have the column type as vachar or nvarchar?

DT_STR equals varchar /varchar2

DT_WSTR equals NVARCHAR2, NVARCHAR

if your source DB Data column type is DT_STR and Destination DB Column type is  DT_WSTR then the data conversion should be the other way around, not "DT_WSR to DT_STR"

Regards, Dinesh

Answer 7

Thanks but no luck.

---

SV

If you add the cast to the source query, then you have to deselect all (changed) columns and then select them again. Otherwise the metadata in the source component will not refresh.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 8

Please check source system and destination system query is having same data type .

Answer 9

Yes they are i finally deleted the table and recreated it

---

SV

Answer 10

Before anyone stumbling on this post gets all crazy trying everything posted here (mostly excellent advice by the way), try simply deleting the oledb source and recreating it. BIDS likes to hang on to metadata sometimes long after it shouldn't be anymore.