Solved

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

Posted on 2011-02-22
7
1,013 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 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

10 Experts available now in Live!

Get 1:1 Help Now