Oledb to Oracle Changes Data Type

  • We're attempting to pull data out of Oracle (never a bad thing) and we're running into an odd issue. The Oracle datatype is NVARCHAR2 which should output to DT_WSTR in the pipeline. When we use the Microsoft OLEDB driver for Oracle, the NVARCHAR2 columns get converted to DT_STR. Further, we get a warning that "Cannot retrieve the column code page from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used." The default code page is 1252.

    When we use the Oracle provided OLEDB driver, the data type is output correctly as DT_WSTR and we don't get a warning. Other than the difference in the driver, both are connecting to the same server, running the same query with the same parameters, including the default code page of 1252.

    Does anyone have any idea what's up with the MS driver?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • What version of Oracle are you connecting to?  I believe that MS doesn't support anything over Oracle 8.  Even if you have an older driver, I've found that different drivers get different results so I've always used the one that works 🙂

  • Thanks for the response. We've just started looking into the driver to see if that could help. I've been running 8i. I think some of the developers were running 9i (not sure about that) and I'm working on getting the 10g OLEDB driver from Oracle installed since we're going against a 10g database now.

    According to the word I got from MS, it's completely up to the OLEDB driver to tell SSIS what the data type & length is.

    When we use the Oracle 8i driver, while it sets the data to the right type, because of how Oracle stores unicode characters, we get a field length that is three times the actual data length. I suspect that we'll be simply moving to the Oracle driver and turning off the warnings. I just figured, surely, we're not the first people moving unicode data out of Oracle and others have hit these issues before us. If we're the first, I see a short article discussing the why's & wherefore's of Oracle data through SSIS in the making...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • It is absolutely the driver. The 8i & 9i drivers both had odd behavior, but after getting the 10g OLEDB driver installed, all of it went away. The data type is now correct in the pipeline and the field length is correct. Thanks again for the help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Regarding the warning about "Cannot retrieve the column code page from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used."

    Setting AlwaysUseDefaultCodePath="TRUE" will cause this error to go away.

     

    Regarding the driver...I've learnt through bitter expereience to aways give the MS driver for Oracle a swerve. I use the Oracle one instead.

    I'd be keen to see a write-up of your experiences Grant.

    Regards

    -Jamie

     

  • Setting AlwaysUseDefaultCodePath="TRUE" will cause this error to go away.

    Yeah, I knew that. Just included it as information. BTW, it only occurred using the MS driver, not with any of the three Oracle drivers.

    As to writing up the experience, I'll try to put something together and include some performance metrics as well. I was thinking about posting it as an article (desperate for ideas).

    Funny thing is, I've always found the opposite to be true, preferring the MS Oracle driver over the Oracle one. Testing everything seems to be the order of the day.

    Thanks for the help,

    Grant

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Where do you find the AlwaysUseDefaultCodePath="TRUE" . I'm using ssis to go from sql to oracle 10g

  • It's on the Properties tab of the OLE DB Source in Custom Properties section.

    We found that loading data to Oracle from SSIS only worked well when we used the Persistent Systems data destination control.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi,

    currently, i'm using MsSQL 2005 32 bits for my project. in my development server i use MsSQL 2005 developer edition with msdaora that work very fine but when i try to migrate to my production server i face some problem because that i'm using Ms SQL 2005 62 bits enterprise edition. In enterprise edition, that didn't come with msdaora but i have try to install mdac to get Oracle Provider for OLE DB. after installing, it prompt me warning messages "Cannot retrieve the column code page from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used." and "convert from unicode to non unicode of data type".

    can anyone advice me on this matter? how am i going to solve this issue. thanks

  • There's a small fix. Set AlwaysUseDefaultCodePage=TRUE on the OLE DB Source adapter.

    -Jamie

     

  • And where is that? I'm stumped. Management studio? Odbc? Help!

  • Mark,

    AlwaysUseDefaultCodePage is a property of the OLE DB Source Adapter.

    OLE DB Source Adapter is a component within the toolbox when building a SSIS data-flow

    You build SSIS data-flows within Business Intelligence Development Studio.

     

    Does that answer the question?

     

    -Jamie

  • D'oh! Thought I was in a different thread. Ignore this post.

  • Not so much. I just trying to do an import when right-clicking the database. The wizard doesn't show any property like that.

  • You're right it doesn't. But you can save the package and edit it offline.

     

    If you think you should be able to set this property within the wizard then lodge a suggestion at connect.microsoft.com/sqlserver/feedback.

     

    -Jamie

     

Viewing 15 posts - 1 through 15 (of 19 total)

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