Solved

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

Posted on 2011-02-22
7
1,011 Views
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.
ssis-package.txt
0
Comment
Question by:parpaa
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
robertg34 earned 500 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.  
0
 

Author Comment

by:parpaa
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.
0
 
LVL 39

Expert Comment

by:lcohan
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.


http://msdn.microsoft.com/en-us/library/ms141704.aspx
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:parpaa
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?
0
 

Author Comment

by:parpaa
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?
0
 
LVL 39

Expert Comment

by:lcohan
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).

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm
0
 

Author Comment

by:parpaa
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now