[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-02-22
7
Medium Priority
?
1,076 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

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

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 40

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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