SSIS Data Conversion Between Unicode and Non-Unicode Error

Question

When I run ssis package I receive this error "cannot convert between unicode and non-unicode string data types", my source is Oracle and when I check my output columns data type they are all Unicode string[DT_WSTR] and my destination which is SQL data type for Input Columns are DT_WSTR . how can I do this conversion?

help are appreciated in advance friends

Solution

Use a particular Data Flow operation (between your source and your destination) that is called Data Conversion. Inside, convert each value from WSTR to STR (they will be added as new columns inside your flow). You will have to supply the length of each string. Keep in mind the data loss between converting from unicode (2 bytes per character) to windows code page 1252 (in this example) which is 1 byte per chatacter.

In this example I'm transforming a column called NAME to a new one called NAME (STR), the new data type is DT_STR and it's length is 50.

You will have to map the new converted column to your table in the destination insert box.

Edit:

This is how you change your mapping in the destination:

You can either drag and drop, or pick from the drop down below.

Comments

[Admin]

Im pretty new in SSIS, how should I do mapping? because I see my destination data type still is DT_WSTR even though I changed my Source data type using the way u said to "string [DT_STR] @Ezequiel

[EzLo]

Source and destination data types are infered automatically when you open your package (by default). SSIS connects to your data sources and checks their data types. If you see WSTR then you have a NVARCHAR (unicode compatible) column in SQL Server or a unicode column in oracle. If both are WSTR then you don't have to convert.

[Admin]

after using Dat Conversion tool between my source and destination inside data flow task, now I have 2 version of each source column, one is normal like Name and the other is Copy of Name I see in mapping

[Admin]

Thank u dear @Ezequiel ... It worked and I accepted as answer, sorry for delay