2

I have a directive to pull code off of an old server and move it to my workstation, and also to update all TFS connections to our new TFS 2017 setup. I found a very important SSIS project that was only maintained by the person I replaced; it was still in VS BIDS 2008, and therefore can't connect to TFS 2017. This means I need to migrate the solution. For the most part, I've done so, but it has an Oracle connection that is giving me trouble, since the original provider is no longer supported. I'm not able to resolve the name when attempting to update the connection. The error is ORA-12154: TNS:could not resolve the connect identifier specified.

What I've done so far:

  • Install Oracle Client 12c with Administrative tools
  • Install the ODAC and ODP.Net
  • Install the SSIS Connectors v5.0 for Oracle
  • Added all server entries to tnsnames.ora and confirmed the syntax of the file
  • Successfully used tnsping to touch the server (20ms ping)
  • Rebooted the machine to satisfy "The I.T. Crowd" checklist
  • Pinged again just to make sure nothing involving network policy modified the files on reboot
  • Opened the old data connection in the connection manager pane of the .dtsx editor in VS 2017 and changed to the Oracle Provider for OLE DB
  • Confirmed the login is correct and the Data Source Name matches the name in tnsnames.ora
  • Confirmed TNSNAMES is in the NAMES.DIRECTORY_PATH variable for sqlnet.ora
  • Looked for a solution online (including here) and found that none quite apply to using the SSIS editor.

I was going to try bypassing the alias via the Data Link Properties and just pasting in the location, but there's a problem there, as well:

The option is inactive!
Also, all of the "advanced" tab is inactive. I can edit values in the "All" tab but the Location property isn't there.

I've hit that "where do I go from here" wall. How can I successfully connect to this datasource?

CDove
  • 1,940
  • 10
  • 19
  • Please confirm that the issue you are having is that you hit `Test Connection` in the connection manager and it returns the error `Unable to connect to the data source`. Is there more to the error? Is this an oledb connection manager. Are you using attunity? Here is the link for attunity: https://www.microsoft.com/en-us/download/details.aspx?id=55179 – Mark Wojciechowicz Feb 16 '18 at 15:06
  • In order to properly view and migrate the project, I did indeed install the SSIS Connectors v5.0 for Oracle from your link. The message is "could not resolve the identifier specified" and is a provider initialization error. I'll add that to the question. This is the Connection Manager with the Oracle OLE DB provider selected, as noted above. I'll add the full text to the question instead of just the ORA code. – CDove Feb 16 '18 at 15:11
  • Have you tried creating a new connection manager and then repoint the source/destination component(s) from the old one to the newly created? – Jayvee Feb 16 '18 at 15:11
  • Yes, I got the same error. This happens when modifying the old connection manager to use the new provider, as well as when creating a new connection manager with the new provider. – CDove Feb 16 '18 at 15:16
  • as this is a 2008 project there might be some 32b/64b issue around. If you have both oracle clients (32 and 64) you might have two tnsnames as well, if that's the case make sure you add the servers to both tnsnames – Jayvee Feb 16 '18 at 15:40
  • The project has already been migrated to VS 2017. Only the 64-bit client has been installed, so it's definitely not a bitness issue. The only tnsnames.ora on the machine is in the directory pointed to by the %ORACLE_HOME% system variable and is read properly by everything else that connects to Oracle on the machine. The underlying OS on the workstation is Win10 x64. – CDove Feb 16 '18 at 15:46
  • Could you add an environment variable for TNS_ADMIN with the path of the tnsnames.ora file. Then restart visual studio and try to connect. This seems to be an answer in some cases – Mark Wojciechowicz Feb 16 '18 at 15:49
  • another thing to try is adding the tnsnames,ora folder ( probably $ORACLE_HOME/network/admin)to the system path environment variable. – Jayvee Feb 16 '18 at 16:07

1 Answers1

2

I found the culprit. Thank you to everyone for your assistance.

The cause was a stumbling block created in the installation and troubleshooting process. When the ODAC installed, it used a different user due to site permissions; the other location was in the system %PATH% above the correct one. By reordering the list so that the correct path to the tnsnames.ora file was on top and rebooting the system, the problem was resolved.

CDove
  • 1,940
  • 10
  • 19