SSIS 2012 - Connecting to Oracle

Question

I wonder if anyone can help with my connection problem.

I've been trying to connect to a remote old Oracle database. The company I work for has only given us Read Only access in the first place.

I'm trying to connect SSIS 2015 to it. I have tried all sorts of things. I checked the version of the database (version 9i which I think is old)

Currently, I can connect using SQL Developer, so I sort of have a client on my PC already. This connects no problem every time

I have installed The ODAC Dev Tools (12.1.0.2.4) , I have created my TNSNames.ORA file. I have set an environment variable too, although not sure what it should be.

The issue is a failed connection messages

ORA01017 invalid username/password

I'm not very strong with Oracle - very rarely use it, so not sure if I need to install a local client version of Oracle first, before I attempt to set up the SSIS connection ?

Anyone have any experience in this ??

Kind regards

Answer 1

Then, finally in Visual Studio, I added a new connection from View menu - Server Explorer - right click on Data Connections, and selected Add Connection...

Click on the Change… button for Data Source, then click the data Provider: drop down…

And Choose .NET Framework data Provider for Oracle, which shows thisa dialog…

Note in the Description that it included Oracle 9i datbases !

Complete the Add Connection dialog box…

This is where new problems arise however (there is a cool workaround for the oncoming problem)…

In the Server name, to get this to work, I enterered a string in the format:- Server name, colon, port number, forward slash, SID name, e.g.

‘MyServerName:PortNumber/SID name’

Click Test Connection and get this…

‘Test Connection Successded’

Click OK to save the connection, and you get the dreaded message…

‘The Given Path’s format is not valid’

Because you can’t have a filename with a colon in it.

I substituted the colon for a hyphen and it saved.

Finally, go to the connection XML file and edit it in the DTS ConnectionString tag, replace the hyphen with a colon, save the XML file and that’s it !!!