SSISDB Catalog: Could not find Oracle connection managers

Question

I have a simple package that imports data from two oracle servers and stores it in a SQL Server. When I execute this package locally, it works perfectly. But when I deploy this package to my SSISDB Catalog, my Oracle connection managers seem to be missing. I also have a proxy account created that I am using to schedule a SQL Server agent job.
I have checked the tnsnames.ora file and I have the right connection setup for both my Oracle connections. I also checked the listener using tnsping and see no errors there.
It is only when I deploy this package that somehow all my connection managers get imported except my Oracle connections.
Can someone please help me fix this?
I have attached pictures below for reference -

Answer 1

Hi @Kalpita Raut ,

It seems that you defined Oracle connections on the SSIS package level instead of Project level.

Remedy:
Right mouse click on top of them one by one and select "Convert to Project Connection" option.

Useful link: using-project-connections-in-ssis

Answer 2

Hi @Kalpita Raut ,

May I know if you run the package in SSISDB, will it fail?

It is only when I deploy this package that somehow all my connection managers get imported except my Oracle connections.

Do you mean it only occurs in this certain package?

If you deploy a simple package with Oracle source, can you see the connection managers in the catalog?

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 3

Hello @Kalpita Raut ,

assumption is that "locally" means you run your package in SSDT/Visual Studio SSIS runtime environment.
If so be aware that this is a 32bit environment- so only 32bit drivers and connector will be used.

If you deploy your project to the SSiS catalog the package will be executed in 64bit mode if your SQL server is a 64bit Edition (most common scenario nowadays)

So you have two option:

1. install 64bit Oracle drivers and on top the appropriate 64bit Attunity connector.

or

2. Execute your package in 32bit - mode. This can be achieved by selecting the "32bit runtime" option in the "Execute Package" dialog or even when you setup the SQL server agent job step

Hope my assumptions were correct.

Regards,
Lars

Answer 4

I was able to resolve this issue by converting my packages to SQL Server 2016 since the Shared SSISDB Server version was 2016.