Post reply

SSIS, Oracle OLE DB Driver, and VARCHARs

  • August 2, 2007 at 1:44 pm

    #175686

    I'm hoping someone can provide some clarity on this. Here goes...
     
    We are building an ETL process in SSIS to pull data from Oracle into SQL Server 2005. I've done extensive research into the OLE DB vs. ADO.NET driver pros and cons and we've decided to go with the Oracle OLE DB driver instead of the ADO.NET driver primarily due to the fact that ADO.NET only returns character data as unicode (NVARCHAR) and we didn't want to convert every character field to VARCHAR.
     
    The source data in Oracle ( 8i ) is non-unicode (VARCHAR2 in Oracle) and the destination is non-unicode (VARCHAR in SQL Server). I have installed the Oracle 10gR2 OLE DB driver (rather than the Microsoft Oracle driver) and can connect to the source systems. Here's where the wierdness is - the OLE DB Source adapter is delivering the character data as UNICODE!!! This makes no sense to me. We went with the Oracle OLE DB Driver precisely so that we didn't have to do data conversion on every field. Is there a setting in the connection manager or on Oracle or somewhere which controls this? I find very little online about this particular problem. 
     
    If anyone can shed some light on this, that would be VERY appreciated.
     
    - Jim

  • August 3, 2007 at 3:06 am

    #724421

    It's the mapping SSIS uses. There is a XML file in the SSIS root which hopes the data type mappings. (Don't fiddle with the file)

    You can open up the advanced editor of the connection object and change the data types in it.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 3, 2007 at 5:27 am

    #724463

    Thanks Crispin,

    What I've discovered is that the Oracle system IS set to Unicode. Not being familiar with Oracle, I expected that a VARCHAR2 was non-unicode, and that a unicode field would be NVARCHAR2. However, if I understand the documentation correctly, the setting is set at a database level, and for this database, it is set to UTF8 - which is unicode.

    So, at the end of the day, my source system is Unicode but the destination is not (there won't be anything but English, so it's not a real big deal). We've made the decision to convert every field - painful!

    Hope this helps someone else...

    - Jim

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply