At the time of data migration or at any situation you may need to add new column and assign fix value to the column. After you have added the column and put the static/fixed value, then you may receiving the error: "Cannot convert unicode and non-unicode string data types"
Let's make an example; business delivered excel file with all data from legacy system which is already closed; and you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.
After you design it found the red error like below that you may hate.
Well, let's look it closer and you can see the error saying as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?
We made a new column by using derived column component called 'Status' and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The staging table has staus column as varchar which cause the issue here.
If you look at the above figure 3, where string "Completed" is set under the Expression so DataType has been picked as [DT_WSTR] which is Nvarchar means Unicode. However, the staging table we have created in the database, the particular column is defined as Varchar which is DT_STR for SSIS means non Unicode. So it's clearly difference between [DT_WSTR] and [DT_STR] in SSIS.
How to solve it?
To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type.
If we explain the expression (from Fig 4), where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).
After you add the above expression the error will disappear and you can run the package to load the data.
Let's make an example; business delivered excel file with all data from legacy system which is already closed; and you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.
After you design it found the red error like below that you may hate.
 |
| fig 1: SSIS package migrated data |
Well, let's look it closer and you can see the error saying as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?
 |
| Fig 2: Unicode and non Unicode miss matched |
We made a new column by using derived column component called 'Status' and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The staging table has staus column as varchar which cause the issue here.
 |
| Fig 3: Data Type issue |
How to solve it?
To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type.
 |
| Fig 4: Solve issue with data conversion |
If we explain the expression (from Fig 4), where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).
After you add the above expression the error will disappear and you can run the package to load the data.
