none
Connection issue to Oracle database from SSIS - VS 2015 RRS feed

  • Question

  • We are trying to establish a connection to an Oracle database from SSIS by using

    ODP.NET, Managed Driver; Downloaded from: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

    In design time we are able to see fields and data from Oracle database but as soon we run the SSIS package in debug mode the following error is thrown.

    Error:

    SSIS package "<h:\visual studio 2015\Projects\ASAM\ASAM\ASAM.dtsx>" starting.

    Information: 0x4004300A at DFT OracleConnection, SSIS.Pipeline: Validation phase is beginning.

    Error: 0xC0208449 at DFT OracleConnection, ADO NET Source [2]: ADO NET Source has failed to acquire the connection {B4AA49CB-948F-471D-9E3B-F1724D206A97} with the following error message: "Could not create a managed connection manager.".

    Error: 0xC0047017 at DFT OracleConnection, SSIS.Pipeline: ADO NET Source failed validation and returned error code 0xC0208449.

    Error: 0xC004700C at DFT OracleConnection, SSIS.Pipeline: One or more component failed validation.

    Error: 0xC0024107 at DFT OracleConnection: There were errors during task validation.

    SSIS package "<h:\visual studio 2015\Projects\ASAM\ASAM\ASAM.dtsx>" finished: Failure.

     

    Friday, June 9, 2017 3:08 PM

All replies

  • Hi Osama,

    As it's more related to SSIS I move it to the specific forum for you to get a quick answer.

    Thanks for your understanding.

    Best regards,

    Fletcher


    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, June 12, 2017 2:10 AM
  • Hi Osama,

    -->>Could not create a managed connection manager

    Do you run the SSIS package under 64 bit mode? If so, please try to go  to Project menu->Debugging tab->Set Run64BitRuntime to False so that the Package run in 32 bit mode.

    Check if it helps.

    Best Regard,

    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, June 12, 2017 7:30 AM
    Moderator
  • Hi Pirlo,

    This is Mark Osama's colleague.  We should have mentioned in the original post that we have tried changing the bit level from 64 to 32 as most of the postings we researched prior had suggested similar however this doesn't help we are still getting same error.

    Regards,

    Mark

    Monday, June 12, 2017 12:46 PM
  • Hi Mark,

    What's the version of ODP.NET driver? Can you try to use a ADO.NET driver or OLE DB for Oracle to connect to Oracle and check whether the issue is caused by driver?

    And please make sure the Oracle Client is 32 bit version.

    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, June 13, 2017 6:00 AM
    Moderator
  • Hi Pirlo,

    Thanks for your responses the following is the resolution to our connectivity issue with some additional details.

    The Oracle Client was not installed on my machine.  After installing the 32 bit Oracle 12g client the VS Oracle drivers became available which were able to connect and retrieve data from the Oracle DB.  The following notes are related to what I found after trying several of the Drivers.  I am planning to develop using the .Net OracleClient Data Provider.

    ADO NET Source:

    Using the .Net Providers\ODP.NET, Managed Driver I am able to connect in design mode and view both fields and preview data without issue.  Also able to run in debug mode for both 32 & 64 bit modes and view data without issue.  However now editing the connection manager crashes Visual Studio with following message:

    Using the .Net Providers\ODP.NET, Unmanaged Driver I am able to connect in design mode and view both fields and preview data without issue.  Also able to run in debug mode for both 32 & 64 bit modes and view data without issue.  No other issues found.

    Using the .Net providers\OracleClient Data provider I am able to connect in design mode and view both fields and preview data without issue.  Also able to run in debug mode for both 32 & 64 bit modes and view data without issue.  No other issues found.

    OLE DB Source:

    Using the Native OLE DB\Oracle Provider for OLE DB I am able to connect to the views and see the columns however a warning is thrown prior about inability to retrieve the column code page. 

    When I try to preview the data the same warning is displayed above and then the following error is thrown:

    I am able to execute the query and see results in the data viewer running in 32 & 64 bit debug mode.

    Using the Native OLE DB\Microsoft OLE DB Provider for Oracle I am able to connect to the views and see the columns however when I try to preview the data the following error is thrown:

    I am able to execute the query and see results in the data viewer running in 32 & 64 bit debug mode.

    Regards,

    Mark

    • Proposed as answer by Mark Fyffe Thursday, June 15, 2017 3:14 PM
    Thursday, June 15, 2017 3:12 PM
  • Hi Mark,

    Thanks for sharing the solution. 

    Would you please mark an appropriate reply as answer if possible?

    Thanks,

    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, June 19, 2017 9:05 AM
    Moderator