derived column cannot convert between unicode and non-unicode string data types

Question

i have a problem with my data flow

my flow is ole db data source-->derived column-->ole db destrination

at the derived column trasformation a add a new column called "entity_code"

the expression for enttity code is:  (DT_WSRT,100)(TRIM(inst_c)+TRIM(dept_c))

the two columns inst_c and dept_c are from the table in my old db source

anyway, i get a message: column "entity_code" cannot convert between unicode and non-unicode string data types.

i tried using import and export wizard but it didnt help. oce i added the derived column i got that message at the destination.

i tried entity_code in DT_STR cast type but that didn't help either.

TIA



Answer 1

What are the destination column's data type in OLEDB Destination? If you make it as NVARCHAR(n) , did it work?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

the data type of entity_code in the destination is DT_WSTR and yet still it doesnt work

Answer 3

No, I meant have you looked into a table in SQL Server database? Make sure that datatypes of both OLEDB Source and Destination are the samein the database.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 4

ok, i looked in the sql server

there are a few columns that are different in the source and destination

for example, the column "inst_c" which "entity_code" is consisted from (and with another column).

"inst_c" in the source table is varchar and in the destination is nvarchar

should all column be the same data types in source and destination?

how can i prevent these kind of problems?

Answer 5

In order to resolve that problem,  you need

1) Make both source and destination to be same data type NVARCHAR(n) or VARCHAR(n)

2) Using Data Conversion control to CAST the source column's data type to the destination.

http://www.bimonkey.com/2009/06/the-data-conversion-transformation/

http://msdn.microsoft.com/en-us/library/ms141706.aspx

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 6

the expression in the derived column is: (DT_WSTR,100)(TRIM(inst_c)+TRIM(dept_c))

which means that this column is DT_WSTR/nvarchar

so i guess it should work

Answer 7

No.

DT_WSTR is SSIS type and NVARCHAR is SQL Server type, don't mix them.

You have no guarantee that destination column in SQL Server is NVARCHAR by just checking the expression.

You need to check:

1. Type of the column in the table used in the destination component (should be NVARCHAR)

2. And type of the external column in the destination component (should be DT_WSTR), check it using advanced editor.

Answer 8

Hi gilboa,

This behavior of Derived Column transformation is by design. As a workaround, you can right click "Derived column" transform on your data flow task, "show advanced editor", select "Input and Output properties" tab, expand "Derived Column ouput" node and then change the data type of the corresponding columns from DT_STR to DT_WSTR. Details about the issue please refer to the link: http://connect.microsoft.com/SQLServer/feedback/details/336139/cant-change-data-type-in-derived-column-transform

Please feel free to ask if you have any question.

Thanks,
Eileen

---

Eileen Zhao

TechNet Community Support