0

I've had a frustrating time attempting to connect to an Oracle DB as a destination via SSIS so I can export some data into it.

I have Oracle ODAC components installed, tnsnames.ora set up properly, TNSNAMES in the NAMES.DIRECTORY_PATH attribute in sqlnet.ora, Attunity installed on my computer etc. I have even managed to connect to the database in question in the SSIS server explorer, and am able to access all its tables etc.

However I can't seem to find out how to actually connect to that Oracle DB as a destination in an SSIS Project. I started a new package, switched to data flow, selected 'OLE DB Destination' and attempted to create a new connection with either 'Oracle Provider for OLE DB' or 'Microsoft OLE DB Provider for Oracle'. Both only let me cite the server name and user name + password, and when I enter the same ones I used to establish the SSIS server explorer connection I get an 'ORA-12154: TNS: could not resolve the connect identifier specified' error.

I have spent a lot of time trying to fix this but keep running into a brick wall. Any idea what I'm doing wrong or what I could do to establish a connection?

Josh Kraushaar
  • 369
  • 5
  • 17

1 Answers1

1

Are you using 32 or 64 bit Windows? I've struggled with some Oracle connections in the past and I made the mistake with mixing up 32 and 64 bit Oracle drivers and settings. Visual Studio is 32 bit and uses those drivers by default, you can go to project properties and change the VS to use 64 bit runtime if necessary.

This could be helpful also: https://stackoverflow.com/a/18098573/5605866

Gigga
  • 555
  • 2
  • 10
  • I attempted to switch the project to use 64-bit, but the standard instructions don't seem to work. It won't let me select 'new' as an option once I make it to the Active solution platform in the configuration manager. – Josh Kraushaar Nov 17 '17 at 21:55
  • With SSIS projects, you use Project Properties -window to change the runtime: https://stackoverflow.com/a/28235255/5605866 – Gigga Nov 18 '17 at 12:11