Question

MuditGupta on Wed, 22 Oct 2014 18:04:54


Hi there,

I am working with a ETL importing data from Oracle to SQL Server using SSIS 2012.  Oracle source data contains special characters which appear as ? or ¿ or � in Toad. 

I know this oracle data is Puerto-Rico Spanish and because of improper character set, these characters are showing as '?' etc.

I do not think it is possible to convert these '?' etc into their original special characters. Had the source data been correct , SSIS would have  easily handled the data correctly.  But I am not a SSIS expert and just wanted to ask around if anyone has dealt with similar issues. 

Thanks



Sponsored



Replies

ArthurZ on Wed, 22 Oct 2014 18:26:30


I suspect the source has its data correctly stored (in Unicode).

You don't need to convert the charters, you should simply also use the Unicode datatype as NVARCHAR in the db and DT_WSTR in SSIS

Visakh16 on Wed, 22 Oct 2014 18:37:54


The reason why you may seeing them as ? etc maybe because client program doesnt support the character set.

MuditGupta on Thu, 20 Nov 2014 14:29:35


I think the problem with source data is the real cause. I tried using DT_WSTR in SSIS pacakge but no luck.

Since source data is missing data itself, SSIS can not reproduce the data. 

Kalman Toth on Thu, 20 Nov 2014 14:58:42


I am working with a ETL importing data from Oracle to SQL Server using SSIS 2012.  Oracle source data contains special characters which appear as ? or ¿ or � in Toad. 

That is pretty similar in SSMS if no support for the special UNICODE characters (except ¿).

You need to examine the source data with a binary tool.

Reference: Converting to Unicode on Microsoft SQL Server and DB2/UDB
Databases

kdinuk on Tue, 25 Nov 2014 17:39:56


Is there any solution for this? I am looking for help. I'm also getting this issue.

Source: Oracle

Destination: SQL Server 2008

I'm using SSIS to get data from Oracle to MSSQL. For few rows, am getting ? or ¿ or � . I want to get back to Special characters. When I open source task and when I put select statment - select companyname from test. Here, am getting these values  ? or ¿ or � in source itself.

Ernest Ostrander on Tue, 25 Nov 2014 23:43:25


First, I suggest posting to a new thread so you can mark an answer and likely get better response
Next, a question... what's the data type in oracle and if not Unicode, what's the characterset?

MuditGupta on Wed, 26 Nov 2014 15:39:51


Are you able to see data correctly in Oracle ? What is the character set for oracle database? 

Use SQL below on Oracle database to get the character set: 

Determine the actual character set: NLS_CHARACTERSET

sql> select * from NLS_DATABASE_PARAMETERS