Converting data is a main part in Data Transformation. In Databases we find many tables with different data types. To convert a datatype in a SSIS package we use Data Conversion tool in ToolBox. But sometimes we face some problems when directly converting data type to another data type using This tool. The error i got mainly is called
"Cannot convert between Unicode and non-Unicode" that is because my source table is containing columns with Unicode data type is Unicode string [DT_WSTR] and i need to convert is to a integer type because it has to be used to be used in a calculated column and we cannot multiply string values.
 |
| Figure of column datatype |
By converting this column by the traditional way by using data conversion tool it gave an error
Above error state that conversion cannot be done because data cannot convert Unicode and non Unicode data.
In order to convert this we need to create a derived column and that column can be the converted into string. and String data can be converted into any datatype
 |
| Figure of column derivation |
in the derived column toolbox we need to create a column like this
(DT_STR,50,1252)<Column name> this will create a column and cast Unicode String column to our new derived column.
