question

Anne-0140 avatar image
0 Votes"
Anne-0140 asked ·

SSIS and oracle provider for OLEDB

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
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

Hi @Anne-0140,

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Anne-0140 avatar image
0 Votes"
Anne-0140 answered ·

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?

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It is part of the SQL Server installation.

0 Votes 0 ·
Anne-0140 avatar image
0 Votes"
Anne-0140 answered ·

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


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Anne-0140,
You need to check your Oracle tnsnames.ora file settings.

0 Votes 0 ·
Anne-0140 avatar image
0 Votes"
Anne-0140 answered ·

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Anne-0140,
Please don't miss the UPDATE section in my answer.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @Anne-0140 ,

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.



· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, I don’t think 1,2,3 are my cases, I can use sql plus to connect the server without problem, I can also create a connection manager using same oracle provider without any connection error. It is just when using the wizard.

Thanks

0 Votes 0 ·

@Anne-0140,
You shouldn't use Answers as comments to previously suggested Answers.
You need to use Comment as your replies.

Please don't miss the UPDATE section in my single answer in this thread.

0 Votes 0 ·
cheong00 avatar image cheong00 YitzhakKhabinsky-0887 ·

Converted to comment to provide better readability.

0 Votes 0 ·