SQL Server : Unicode / Non Unicode Data types

I write this topic to give you a quick tips when you're facing to some errors messages like 'cannot convert unicode to non unicode ....'

In SQL Server, unicode character is encoded in 2 bytes rather than 1 byte for a non unicode character.

In SQL Server, unicoded datas type are prefixed by 'n' : nvarchar, nchar, ntext (deprecated because will be remove in later version of sql server).

They are the same as varchar, nchar,text except that :

- they can handle up to 4000 characters  instead of 8000 characters for varchar,char,text.

- take more memory space

- can support a wider range of characters.

Sometimes in SSIS, the source is captured as a DT_STR ( non unicode ). A classic problem is that the table column is set to nvarchar ( unicode ) data type. A problem occurs when executing the task.

To resolve that issue, 2 solutions :

1- change the table column data type to varchar ( non unicode ) - it's working but I think that it's a dummy and dirty solution.

2- create a temporary output column of type DT_WSTR ( unicoded ), code some inputprocessrow business logic  and map the table column of type nvarchar to that temporary column.