Connecting Oracle Database with SSIS

Question

Post reply

Connecting Oracle Database with SSIS

WilburSmith

Hall of Fame

Points: 3219
More actions
February 9, 2024 at 7:54 am

#4375776

Hi,
I have Visual Studio 22 and I am trying to connect to Oracle database among other sources.
I can not see Oracle as an alternative when creating OleDB data sources.
I installed 32 bit 'Oracle Client for Microsoft Tools' which my client provided for me ==> no luck
I googled this and that and came to the conclusion that the cause was that 32 bit version since the VS is 64 bit
I installed 64 bit 'Oracle Client for Microsoft Tools' that I found ==> no luck either (well I haven't rebooted after the installation...)
I also set the TNS_ADMIN env variable but this is of no use before I can try to connect first....
Ville

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WilburSmith Hall of Fame Points: 3219 More actions · Answer 1

Hi again,

Now I downloaded ODAC 19c from https://www.oracle.com/database/technologies/net-downloads.html

I took both 32 and 64 bit versions.

Like said, the VStudio is 64 bit. That makes me think the 64 bit version would be the correct one.

But then again, I read from somewhere that while running the packages in VS, it would use/seek the 32 bit Oracle dll's etc...

I tryto figure/find this out before installing anything.

Ville

frederico_fonseca SSCoach Points: 16017 More actions · Answer 2

VS2022 is (or has) a 64 bit version - this is a first - all prior versions were 32bit. so you need to check what version you did install (64 or 32) and install all required oracle components with same bitness.

if you do install both 32 and 64 bit, 32 bit should be the first to be installed, then 64 bit.

see these 2 links - they may help you

https://www.oracle.com/a/ocom/docs/database/adb-ssis-sdt.pdf

https://www.oracle.com/database/technologies/appdev/ocmt.html

also take in consideration that the oracle "default" install does not install all components - so you may need to select custom install and install remaining connectors.

This reply was modified 2 months, 2 weeks ago by frederico_fonseca.

Ed B Hall of Fame Points: 3618 More actions · Answer 3

Have tried the Microsoft Connector for Oracle? I found it worked right away, no need for an Oracle client or tnsnames. In the past I have failed miserably in trying to set up Oracle connections. The notes say you need the appropriate version for both VS and SQL. I'm using VS 2019 and deploying to SQL 2022, but I only installed the 2022 version and it works in debug and from the catalog.

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

WilburSmith Hall of Fame Points: 3219 More actions · Answer 4

Ed,

Thanks, this 'Microsoft Connector for Oracle' worked for me fine

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

('ODAC 19c' also brought the Oracle OleDb choice to the the OldDB list but itdid not work for me)

Ville

WilburSmith Hall of Fame Points: 3219 More actions · Answer 5

Hi,

Yep, it (Microsoft Connector for Oracle V1.3 ) worked but it does not when run from inside the JOB....

Run error 2

My friends have tried it on the same server and it works when the package is run with V Studio

What could be the reason for this? ...why it is trying ODBC...?

They say that both 32 and 64 bit versions are installed

I am not 100 percent sure but possibly they have version 1.2 on the server though...

Ville