SSIS and oracle provider for OLEDB

Anne 276 Reputation points
2021-03-09T20:22:11.473+00:00

I am using Visual studio 2019 to create SSIS package.

Since I want to do a data flow and I want to use the SSIS Import and export wizard so that it creates the destination table easily.

But when I chose data source oracle provider for OLE DB, it gives me the error below, is it some bug that the wizard not work with oracle provider for oleDB? I had the oracle driver client installed.
And if I manually create a data source using same driver, there is no error.
But for my purpose I want to use the wizard to achieve the result. any ideas why this error?

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.

ADDITIONAL INFORMATION:

ORA-12560: TNS:protocol adapter error (OraOLEDB)

BUTTONS:

OK

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,116 Reputation points
    2021-03-09T21:34:47.753+00:00

    Hi @Anne ,

    There are two editions of the SQL Server Import and Export Data wizard: 32-bit and 64-bit.
    Please try both of them.
    One of them should work depending on what Oracle Client edition (32-bit or 64-bit) installed.
    You can see both of them via Windows OS Start button, Microsoft SQL Server 2017, etc.

    UPDATE
    It could be that the Oracle is down.
    Check it out here: ora-12560-tnsprotocol-adaptor-error.

    loadimportwizard.png

    1 person found this answer helpful.
    0 comments
  2. Anne 276 Reputation points
    2021-03-09T21:50:33.543+00:00

    Thanks, I am using SQL server 2019.
    And the tool of the wizard not showing up in my start menu.
    Is it part of server install or SSMS install?

  3. Anne 276 Reputation points
    2021-03-09T21:55:09.547+00:00

    I found the two on my server start menu, I tired both 32 bits and 64 bits for oracle provider for OLEDB same error.
    And I also have both oracle driver 32 bits and 64 bits intalled.

    TITLE: SQL Server Import and Export Wizard

    The operation could not be completed.

    ADDITIONAL INFORMATION:

    ORA-12560: TNS:protocol adapter error (OraOLEDB)

    BUTTONS:

    OK

  4. Anne 276 Reputation points
    2021-03-09T23:31:13.033+00:00

    There should not be problem in tnsnames file. For if I manually create a connection manager using the same oracle provider, there is no problem.

  5. Monalv-MSFT 5,896 Reputation points
    2021-03-10T03:40:57.877+00:00

    Hi @Anne ,

    When you see this ORA-12560 error on Windows, you need to check these two things before anything else.
    1.Make sure the windows service is up and running
    2.Make sure your ORACLE_SID is set to the instance name you’re trying to connect to.
    3.If either one or two are not correct you will get the error as seen below.

    Please refer to the following links:
    How to Fix ORA-12560: TNS: Protocol Adapter Error
    How to fix OLE DB connection error while connecting Oracle database using SSIS.

    Best regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.