String Unicode and NVARCHAR

Question

Hello,

I must extract data from a xml file and load it into an Oracle 11g database. I have a series of output columns with type "unicode string". These output columns are mapped with nvarchar2 columns of a table in the database. I have the error "column [name] cannot convert between unicode and non-unicode datatype". I open the "Show Advanced Editor" of "OLE DB Destination" to change dataype of external columns of "OLE DB Destination" to unicode string but I can't, the change is not saved. On this page (http://www.mssqltips.com/sqlservertip/1393/import-excel-unicode-data-with-sql-server-integration-services/), I read this is normal because "you can not change the data type in the actual table". But the datatype of the columns is nvarchar2 and supports unicode ! If I use a data conversion, it works perfectly but it takes a lot of time. If it's possible, I would like to avoid using data conversion.

I hope my question is understandable, I rarely write in english. Thanks in advance for your assistance.

Maxime302

Answer 1

Which is the SSIS datatype in the dataflow?

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 2

Unicode String DT_WSTR.

Answer 3

Which OLE DB provider are you using?

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 4

I'm using "Native OLE DB\Microsoft OLE DB Provider for Oracle".

Answer 5

I'm using "Native OLE DB\Microsoft OLE DB Provider for Oracle".

Can you try the Oracle OLE DB provider? I don't know if you have installed that one.

It seems your datatypes in SSIS itself are correct, so I'm searching for other culprits here.

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 6

It works perfectly. Wonderful ! Thank you for the answer.