Solved

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

Posted on 2011-02-22
7
1,023 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Degrading on Write 13 70
how many extra RAM for SQL server is needed 22 43
Unable to save view in SSMS 21 73
What is this datetime? 1 19
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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