2

I am having an issue with Oracle connectivity from an SSIS project, and have been Googling around to try to find a solution for over a week, now, and haven't found anything.

I have the following configuration in my dev environment:

O/S: Windows Server 2008 R2 Datacenter, 64-bit SQL Server Data Tools, 2010

I need to configure an SSIS Data Flow Task to pull data from four different Oracle Data Sources (among others). One is Oracle version 11.1.0.7, two are version 9.2.0.4.0 and one is 9.2.0.8.0.

We use local TNSNAMES and SQLNET.ora files for our configuration.

I have tried this with many different Oracle clients, and basically get the same result, so will highlight one that I did:

  • Installed the Oracle Client 11gR2 64 bit.
  • Installed the Oracle Client 11gR2 32 bit (that order is recommended).
  • Copied my TNSNAMES and SQLNET.ora files into both network/admin configuration folders.
  • Did a successful TNSPING on each of the 4 databases named above. Successful.
  • Opened up SQL Server Data Tools and opened a new project
  • Did a "Create new Connection Manager", picked ADO.NET
  • Selected "Oracle Client Data Provider", enter the details of the 11g database
  • Did a "test Connection". Success.
  • Repeat the procedure for one of the 9.0 databases. When I do the "Test Connection" I get a "Test Connection failed because of an error in initializing provider. ORA-12645: Parameter does not exist".

I get this same result for all three of the 9.x databases.

Oracle "help" says that this error is caused by a missing parameter in the SQLNET.ora file, and that the solution is to add the missing parameter. Trouble is, nowhere does it say what is missing.

I have tried this same procedure with other Oracle client installs other than the two clients - ODTwithODAC1120320_32 bit, ODAC1120320_x64 and get exactly the same result. I can't go to a version 12 client because as I understand it, it won't work with a 9 database. I can't go to a 10 client because that won't work with an 11 database.

I have a feeling that it's very specific to the 64-bit O/S and version of SQL server, but that is our target platform, so need to make it work.

Can anyone help me with this, please?

user3306611
  • 21
  • 2
  • Can you install a 9 client in parallel? I recall seeing multiple versions of clients installed on our server and they all work happily in parallel. Do the 9.0 database behave any better through ODBC? i.e. create an ODBC connection, test, then point SSIS at the ODBC connection. – Nick.Mc Feb 14 '14 at 01:17
  • ADO.NET would be my last choice - its so slow. I prefer the Microsoft Connector for Oracle by Attunity , Oracle Provider for OLEDB or Microsoft OLE DB Provider for Oracle (in that order). – Mike Honey Feb 14 '14 at 01:24

0 Answers0