Converion problem b/w unicode and non-unicode strings in ssis

Posted on 2011-02-22
Medium Priority
Last Modified: 2012-05-11
Hello Experts,

I am trying to export data from our sql server 2005 to Oracle 10 g database using SSIS. Within Data flow task i am getting the unicode conversion error.

"Cannot convert between unicode and non-unicode data types"

I tried data conversion tab in between but even that doesnt work. also I manually deleted the columns and replaced them with DT_STR data type making sure they are both same on source and destination. BUt still the problem exists. Not sure if i am heading in the correct direction.
Question by:parpaa
  • 4
  • 2

Accepted Solution

robertg34 earned 2000 total points
ID: 34954841
You are better off changing the source or destination to match data types.  For example nvarchar in sql server (unicode) corresponds to as nvarchar or nvarchar2 type in Oracle.  I've not had much luck with the data conversion transformation in this scenario.  

After you change the data types, you will also want to drop and re-add the source or destination data flow boxes.

One other alternative is to use a query to convert the column to the proper type.  Do this with a new data flow source or destination though.  Doing that ensures you get clean meta data.  

Author Comment

ID: 34955125
i am sure the data types are correctly mapped as in

varchar - varchar2
nvarchar - nvarchar2
I tried to change them manually in the advanced editor of either source or destination but it reverts back immediately to previous data type. (i.e DT_STR to DT_WSTR) and viceversa.. I am sure i am seing problem on all the varchar dat type columns.
LVL 40

Expert Comment

ID: 34955916
Please see all legal/ilegal CASTs at link below and keep in mind you may need an explicit CAST AS... to accomplish what you are doing. In my opinion it is easier to create a SQL view with ALL the explicit columns casted to match ORACLE destination columns datatype.

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.


Author Comment

ID: 34956191
My source data type is DT_STR and after the data converion tab it still remains the same and whn i try to change the data type on OLDB destination advanced properties by deleting the collumn manually and then creating it with the same "DT_STR" data type and once i hit ok, it reverts back to the old data type DT_WSTR. is there somewhere else i need to change this option?

Author Comment

ID: 34956443
I think i found the issue but not sure how to fix it. So all the problematic columns are of 'varchar' data type in source and their equivalent data type in oracle is 'varchar2'. The same column in some other table has 'nvarchar' datatype in source and its equivalent data type is 'nvarchar2' in oracle. So going by this theory it seems like the varchar2 has data conversion problems. Now i tried to go to oracle end (destination) and change the data types to nvarchar2. But that didnt work either. any ideas?
LVL 40

Expert Comment

ID: 34964236
source varchar is ANSI SQL standard VS. target varchar2 which is Oracle standard max 4000 bytes
source nvarchar is SQL UNICODE  vs target NVARCHAR2 UNICODE maximum length 4000 bytes.

Use CLOB and NCLOB on oracle side instead of VARCHAR2/NVARCHAR2
The LOB datatypes for character data are CLOB and NCLOB. They can store up to 8 terabytes of character data (CLOB) or national character set data (NCLOB).


Author Comment

ID: 34970658
I fixed this problem by casting the source as nvarchar and then mapping with DT_WSTR data type. I tried this before also but never worked. This time i deleted the connection flow task completely and recreated and it worked this time.. Thanks for your help.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question