Answered by:
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
Sunday, November 11, 2012 11:52 AM
Answers
-
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,
EileenEileen Zhao
TechNet Community Support
- Marked as answer by Eileen Zhao Monday, November 26, 2012 6:44 AM
Wednesday, November 14, 2012 9:14 AM
All replies
-
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
Sunday, November 11, 2012 11:54 AM -
the data type of entity_code in the destination is DT_WSTR and yet still it doesnt workSunday, November 11, 2012 12:11 PM
-
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
Sunday, November 11, 2012 12:15 PM -
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?
Sunday, November 11, 2012 12:22 PM -
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
Sunday, November 11, 2012 12:28 PM -
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
Sunday, November 11, 2012 12:34 PM -
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.
Monday, November 12, 2012 2:22 AM -
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,
EileenEileen Zhao
TechNet Community Support
- Marked as answer by Eileen Zhao Monday, November 26, 2012 6:44 AM
Wednesday, November 14, 2012 9:14 AM