SSIS Package Error - cannot convert unicode to non unicode

1
votes

I am new in SSIS Package development. Since few days I am facing an unexpected issue with SSIS package.

My SSIS Package is developed with using two different database. In Source, I have used SQL Server and Oracle and in destination I've used Oracle. SSIS Package was working as expected since few years but one of my client upgraded his server from 32bit to 64bit operating system.

Issue is with using Oracle database. Earlier, I was using MSDORA.1 for connectivity of Oracle database but one of Microsoft forum, I read that they've not launched MSDORA.1 yet. So I have to convert my SSIS package with OraOELDB.Oracle.1 drivers. Everything is working as expected in one of the server but when I deployed on client's server it is giving me below error.

<<MY_COLUMN>> cannot convert unicode to non-unicode string data types

I had VARCHAR2 data type in all columns of Oracle database table.

1
.netsql-serverunicodessisoracle11g
just because something is a varchar doesn't mean it's a UNICODE varchar. - Marc B
@MarcB: Thanks. I understand that we have to add some data type conversation in SSIS Package but as I told you I am facing this issue with Client's server only. So wanted to understand what is exactly happening. - user968441
you say the client upgraded their server. so it's not the same environment as before. make SURE the schema in their server exactly matches the schema on your test/devel server. - Marc B
@MarcB: I've verified almost all the configuration between both servers (Mine and Client). I dint found any variation on those servers which can cause any issue regarding this. - user968441

1 Answers

2
votes

I have experienced the same when changing drivers. The OLEDB drivers for Oracle will give you "DT_WSTR" types in your SSIS. Some other drivers (e.g Attunity) will give you "DT_STR" types for the same fields.

You need to add a step to your SSIS Package that converts those fields to the "DT_STR" data type. You need to convert or cast. This thread contains more information for you: SSIS Package: convert between unicode and non-unicode string data types