Problems with Oracle character setting

Hi there,

I am using an ADO Oracle Connector to store Oracle data to an SQL Server.
When I map the input data to the OLEDB destination I get the Error msg that
unicode (DT_WSTR) cannot be mapped to 1292 character code (DT_STR).

To solve this I put a Data Conversion Transformation between Source and Destination.
That would mean a lot of work if I couldn't map the data directly from Source to Destination
for all the tables of my project.

I checked the NLS-Settings from the Oracle db:
select * from sys.v_$nls_parameters
NLS_CHARACTERSET -> WE8ISO8859P1

It's really a mystery to me why the DataReader converts Latin-1 to unicode which has to be
converted to Latin-1 again.
Could someone please help me out?

Fridtjof
The problem here is with managed code. The CLR doesn't have a ANSI type string so all strings are converted to Unicode. So in actuality, it is not the datareader that does the conversion but Oracle's ADO.NET connector. Obviously, knowing this doesn't really help you out all that much though. Is there any way you can make the SQL Server table be unicode instead of ANSI. If not then data conversion is the only solution other than to get an OLEDB driver for Oracle, since OLEDB does support ANSI directly.

HTH,
Matt|||Matt,

you're right. I've tried to download Oracle data via OLEDB which nags that it cannot read Oracle's character setting and assumes it to be 1292 (or was it 1252?). But it downloads the data correctly.

Unfortunately I cannot set the password property in a Package configuration. See this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161564&SiteID=1

This way I'm not getting much further. One have to ponder between the different restrictions :(
Thanks anyway
Fridtjof