locked
SSIS And Sybase Source OLEDB connectivity issue with code page for character set RRS feed

  • Question

  • Hi

    I am using SSIS 2008 to connect the Sybase Server version 15.0.3 64-bit. The default character set id used by this Sybase server is 4 (i.e. roman8). (http://manuals.sybase.com/onlinebooks/group-charc/chg0300e/charsets/@Generic__BookTextView/1706;pt=266)

    I had installed Sybase Client for Sybase OLEDB Driver (ASEOLEDB) to integrate this in OLEDB Source Editor.

    As per guidance from one of the past MSDN forum discussion, I tried setting property of AlwaysUseDefaultCodePage property to "FALSE"  (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5af9328-e1fb-48d7-a85d-1d08bb7cf0e5/).

    However, I am still experiencing error message as follows:-

    *********************************************************

    Error at Package2 [Connection manager "xxxxxx.xxxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[00000]
    [Native Code: 30061]
    [ASEOLEDB]Could not load code page for requested charset".
    An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[1ZZ001]
    [Native Code: 30016]
    [ASEOLEDB]Internal Error".

    Error at Data Flow Task 2 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxxxxxxxxx.xxxxxxxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

    ------------------------------
    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

     ********************************************

    Please advise if anyone has encountered similar issue and found any resolution on this.

    Appreciate your help....

    Regards,

    Ketan

     

     

     

    Wednesday, September 1, 2010 12:32 AM

Answers

  • Hi Arthur,

    I have found workable solution, let me know if this has any performance side-effects while pulling huge data set from Sybase.

    Here are the things that I did:

    1. I created System DSN in ODBC Administrator using Sybase OLEDB Driver

    2. Then I configured ADO.NET Connection manager in SSIS using this System DSN

    3. Finally I used ADO.NET Source Editor in SSIS to fire small query on one of the Sybase tables and it worked.

    Please advise if this solution has any known side-effects.

    Regards,

    Ketan

    ************************************************

    Microsoft .NET Framework has some issues with SSIS packages using 3rd Party ODBC drivers

    http://blogs.msdn.com/b/sqlblog/archive/2009/04/09/after-installation-of-net-framework-3-5-sp1-or-net-framework-2-0-sp2-ssis-packages-using-odbc-3rd-party-drivers-may-fail.aspx

     *************************************************

    Guys,

    With the Help of work around from Microsoft MSDN blogs, I am now able to extract character data as well from the Sybase using SSIS ADO.NET (over ODBC).

    I am marking this as answer. Please let me know if anyone has any issues.

    Thanks to all.... Esp Arthur, James, Vinu, CMK

    Regards,

    Ketan

     

    • Edited by Ketan Patil Friday, September 3, 2010 5:07 AM Work Around for Character Set Data from Microsoft
    • Marked as answer by Ketan Patil Friday, September 3, 2010 5:07 AM
    Wednesday, September 1, 2010 11:32 PM

All replies

  • Is this a 64 bit SSIS server? Because to my knowledge there is only a 32-bit driver for Sybase.
    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    Wednesday, September 1, 2010 1:47 AM
  • Can you check out this link and see if any installation is missing

    http://www.sybase.com/detail?id=1040377

     


    vinu
    Wednesday, September 1, 2010 1:55 AM
  • Hi Ketan,

    I remember I was dealing with something similar, please try setting the code page to the value of 4 - roman 8. Please let me know what happens.


    Arthur My Blog
    Wednesday, September 1, 2010 2:47 AM
  • Hey James,

    Yes, I have set the property of Visual Studio IDE to Runtime64bit as "False". I suspect OLEDB driver for Sybase could be the issue.

    Thanks,

    Ketan

    Wednesday, September 1, 2010 9:09 PM
  • Hi Vinu,

    Thanks for the link on recommended ODBC drivers. Let me see if I can find out ODBC or OLEDB driver for Sybase.

    Thanks,

    Ketan

     

    Wednesday, September 1, 2010 9:11 PM
  • Hi Arthur,

    I tried setting Code page value to 4 after which I got different error that says "[ASEOLEDB]Data source not found and no default driver specified".

    Here is the complete error message -

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at Package [Connection manager "xxxxxx.xxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E19.
    An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80040E19  Description: "[IM002]
    [Native Code: 30136]
    [ASEOLEDB]Data source not found and no default driver specified".

    Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxxxxx.xxxxxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

    ------------------------------
    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

    ------------------------------

    I will do further research on this error . Let me know if you have anything for me to try out.

    Regards,

    Ketan

     

    Wednesday, September 1, 2010 9:18 PM
  • Thank you for the update,

    Are you using the .net native provider? If not you should.


    Arthur My Blog
    Wednesday, September 1, 2010 9:20 PM
  • Hi Arthur,

    I have found workable solution, let me know if this has any performance side-effects while pulling huge data set from Sybase.

    Here are the things that I did:

    1. I created System DSN in ODBC Administrator using Sybase OLEDB Driver

    2. Then I configured ADO.NET Connection manager in SSIS using this System DSN

    3. Finally I used ADO.NET Source Editor in SSIS to fire small query on one of the Sybase tables and it worked.

    Please advise if this solution has any known side-effects.

    Regards,

    Ketan

    ************************************************

    Microsoft .NET Framework has some issues with SSIS packages using 3rd Party ODBC drivers

    http://blogs.msdn.com/b/sqlblog/archive/2009/04/09/after-installation-of-net-framework-3-5-sp1-or-net-framework-2-0-sp2-ssis-packages-using-odbc-3rd-party-drivers-may-fail.aspx

     *************************************************

    Guys,

    With the Help of work around from Microsoft MSDN blogs, I am now able to extract character data as well from the Sybase using SSIS ADO.NET (over ODBC).

    I am marking this as answer. Please let me know if anyone has any issues.

    Thanks to all.... Esp Arthur, James, Vinu, CMK

    Regards,

    Ketan

     

    • Edited by Ketan Patil Friday, September 3, 2010 5:07 AM Work Around for Character Set Data from Microsoft
    • Marked as answer by Ketan Patil Friday, September 3, 2010 5:07 AM
    Wednesday, September 1, 2010 11:32 PM
  • In OLEDBSource we have one property AlwaysUseDefaultCodePage .Default this value is false...change this property=TRUE.

    If it is wrong  leave it.

    Thanks

    CMK.. 

    Thursday, September 2, 2010 3:03 AM
  • Hi Ketan,

    Thumbs up for the ADO.Net, but you need to be aware that at this point the solution is more constrained by your hardware e.g. the runnable memory.


    Arthur My Blog
    • Proposed as answer by ArthurZ Thursday, September 2, 2010 3:09 AM
    Thursday, September 2, 2010 3:09 AM
  • Hi Arthur,

    I have configured character set for System DSN (for Sybase) in ODBC administrator as "Client Default" however using this when I try to pull string data using SSIS (ADO.NET Source editor) it fails. This issue does not occur for numeric and datetimestamp data types from sybase to SQL.

    I have tried Character set options for "Server Default" & "User Specified" in System DSN but this did not work like the "Client Default" option.

    Any suggestions that you have got for me.

    Thanks,

    Ketan

    Thursday, September 2, 2010 3:54 AM
  • CMK,

    I tried this option however it throws error message "[ASEOLEDB]Data source not found and no default driver specified".

    Let me know other options if any.

    Rgds,

    Ketan

     

    Thursday, September 2, 2010 4:56 AM
  • Hi,

    I had the same problem recently, and I followed everything that is suggested here, and unfortunately i couldn't make it work. 

    However I finnaly succeeded using Linked server with the Sybase DB.

    I'm just giving this as idea if someone got stuck like I was.

     

    Thank you all for the valuable informations you provided me.

    Best regars,

    Josip


    Wednesday, May 4, 2011 7:32 PM