locked
SSIS connectivity to oracle DB RRS feed

  • Question

  • Hi,

    I am getting the below mentioned error in one of the task in SSIS package.

    ----------

    Exception from HRESULT: 0xC0202040
    Error at Populate data combined data [OLE DB Destination [81]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
    An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E21  Description: "Multiple-step 
    OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Error at <Task Name> [OLE DB Destination [81]]: Failed to open a fastload rowset for ""<Table Name>"". 
    Check that the object exists in the database.

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

    I have Microsoft visual studio 2010 shell,  server management studio 2012 installed in my machine and also I have installed ODTwithODAC121012 and attunitySSISOraAdapters for the  DB connectivity. 

    I tested the connection to the oracle DB and it is success.

    Please let me know if there is something wrong with the connection setups. 

    Thanks in Advance!!



    • Edited by SukanyaCu Friday, January 19, 2018 10:15 AM
    Friday, January 19, 2018 10:05 AM

Answers

  • Hi All, 

    The problem was with the ODTwithODAC software.

    Oracle Provider for OLE DB 12.1.0.2  supports the IRowsetFastLoad interface, which enables fast memory-based bulk-copy operations in the Oracle database.

    I installed the ODTwithODAC which contains Oracle Provider for OLE DB 12.1.0.2 , it resolved the issues.

    Thanks !!

    • Marked as answer by SukanyaCu Friday, February 2, 2018 1:13 PM
    Friday, February 2, 2018 1:13 PM

All replies

  • What is the edition of the SQL Server you're using?

    Also what version of Attinuity driver you installed?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 19, 2018 10:18 AM
  • Microsoft SQl server 2008 and Microsoft SQl server 2012 is installed in machine.

    Microsoft Connector for Oracle by Attunity Version 1.2 

    Friday, January 19, 2018 12:01 PM
  • Microsoft SQl server 2008 and Microsoft SQl server 2012 is installed in machine.

    Microsoft Connector for Oracle by Attunity Version 1.2 

    I'm asking on the edition. Whether it is standard, developer etc



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 19, 2018 12:17 PM
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0  Enterprise Edition (64-bit) 
    Friday, January 19, 2018 1:33 PM
  • Hi SikanyaCu,

    Please make sure you have set the correct TargetServerVersion in SSDT project and ensure the version of debug is matched with your version of oracle driver. And Also you can connect to oracle via CMD on local machine.

    Then try to use 'Oracle provider for OLEDB' instead of 'Microsoft Provider for Oracle'. Or use ADO.NET, check what happens.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 6:01 AM
  • Hi,

    Please check if the installed version of softwares are correct  for the below mentione version of visual studio and Sql server which is in my machine.

    I am using 64bit- windows 8.1 enterprise.

    Sql server : Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Microsoft visual studio : Microsoft visual studio 2012 shell version 11.0.50727.1

    32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studiobase.
    ODTwtihODAC : ODTwithODAC1120320_32bit

    64-bit Oracle Data Access Components (ODAC) 
    ODAC121010_x64 

    Attunity :  I installed attunity 1.2 32 and 64 bit.

    I am able to connect to the oracle DB and able to fetch the data. but the problem is in data access mode of the destination oracle server. It's throwing error when I set the data access mode as table or view - fast load.

    Can anyone please help to resolve this issue. Is there any setting which I need to change in visual studio after installing attunity. 

    Thanks in advance.

    Tuesday, January 23, 2018 1:48 PM
  • If the task is a Data Flow and you are querying Oracle try to set the AlwaysUseDefaultCode to True. It's just a try...but could be the problem.

    Please mark as answer if this post helped you

    Tuesday, January 23, 2018 2:04 PM
  • Hi Diegoctin,

    I tried it , but it's not working.

    Please let me know if settings need to be changed after installing attunity.

    Thanks,

    Sukanya

    Tuesday, January 23, 2018 2:59 PM
    • In the registry, under the key for an OLE DB provider's CLSID, there may be an entry named OLEDB_SERVICES. If the OLE DB provider that is used to make the ADO connection does not have the OLEDB_SERVICES entry, and ADO tries to set up a property that is not supported by the provider, the error occurs. For more information about this registry entry, see the "Resolution" section.

    Just add the OLEDB_SERVICES and it will work

    Please mark as answer if this post helped you

    • Proposed as answer by Pirlo Zhang Thursday, January 25, 2018 6:43 AM
    Tuesday, January 23, 2018 3:11 PM
  • Hi Diegoctin,

    Could you please let me know how to get the  OLE DB provider's CLSID ??


    Monday, January 29, 2018 7:34 AM
  • Hi SukanyaCu,

    Is there any variables referenced in your connection string?

    If so, try to set DelayValidation property to true in the Data Flow Task properties. 

    Another possible way is to set ValidateExternalMetadata to false in OLE DB destination.

    Or try to use Oracle Destination, see: Using the Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services

    Check if the issue persist.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 29, 2018 7:59 AM
  • Hi Pirlo,

    The issue is with data access mode - Fastload  option. I installed microsoft attunity 1.2, but the oracle destination does not show in the tool box. I am using visual studio 2010 shell (integrated) . In the tool box, oracle destination option is not there. Please let me know if I need to install visual studio enterprise edition.I have sql server 2008 enterprise edition installed in my machine. 

    Tuesday, January 30, 2018 7:42 AM
  • Hi Pirlo,

    The issue is with data access mode - Fastload  option. I installed microsoft attunity 1.2, but the oracle destination does not show in the tool box. I am using visual studio 2010 shell (integrated) . In the tool box, oracle destination option is not there. Please let me know if I need to install visual studio enterprise edition.I have sql server 2008 enterprise edition installed in my machine. 

    Attinuity connector comes with Enterprise Edition 

    See system requirements here

    https://www.microsoft.com/en-us/download/details.aspx?id=29284


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 30, 2018 7:49 AM
  • Hi Visakh,

    Sql server enterterprise edition - 2008 is installed, but fast load option not working.


    • Edited by SukanyaCu Tuesday, January 30, 2018 10:21 AM
    Tuesday, January 30, 2018 9:26 AM
  • Hi Visakh,

    Sql server enterterprise edition - 2018 is installed, but fast load option not working.

    Can you check if you've installed correct version of Attinuity as per this?

    http://www.danielbowlin.com/ssis-optimization-techniques-part-10-ssis-oracle-source/

    Also refer the other link for how to configure it to ensure you did the same steps


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 30, 2018 9:34 AM
  • Hi SukanyaCu,

    Try to manually install Attinuity assembly into GAC then refresh SSIS toolbox.

    Check what happens.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 30, 2018 10:06 AM
  • Hi All, 

    The problem was with the ODTwithODAC software.

    Oracle Provider for OLE DB 12.1.0.2  supports the IRowsetFastLoad interface, which enables fast memory-based bulk-copy operations in the Oracle database.

    I installed the ODTwithODAC which contains Oracle Provider for OLE DB 12.1.0.2 , it resolved the issues.

    Thanks !!

    • Marked as answer by SukanyaCu Friday, February 2, 2018 1:13 PM
    Friday, February 2, 2018 1:13 PM