I have an SSIS package which has 4 connections - a WebService, 2 SQL Server connections (across 2 domains), and 1 connection to an Oracle DB.
When the package is run from Visual Studio from a laptop, it runs OK. When I deploy it to the server, I'm getting 'TNS:could not resolve the connect identifier specified'. Results of tnsping's:
64 Bit Version of TNSPing
C:\oracle\product\10.2.0\client_2\BIN>tnsping myservice.name
TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 05-SEP-2
013 11:06:10
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
c:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = ww.x.y.zzz)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myservice.name))
)
OK (0 msec)
32 Bit Version of TNSPing
C:\oracle\product\10.2.0\client_1\BIN>tnsping myservice.name
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 05-SEP-2
013 11:06:20
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
c:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = ww.x.y.zzz)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myservice.name))
)
OK (0 msec)
Now a similar experiment using DTSWizard.exe.
Using Oracle Provider for OLE DB. Get same results with Microsoft OLE DB Provider for Oracle.
Please see this image - I've not enough rep to post the image here..!
(I've also tried using C:\PROGRAM~2\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe - no joy.)
The laptop is 64-bit, and has Oracle 11.2.0 installed.
The server is 64-bit, and has Oracle 10.2.0 installed.
My understanding is that SQL Server Management Studio is 32-bit only - could this be where the problem lies?
Could anybody suggest where I could go from here? I've tried various connectors, none of which seem to make a blind bit of difference. The only other option I can think of is taking the drivers from the server, putting them on the Laptop, re-configuring the SSIS package to work with those, and then deploy it again -- but, I'm hoping to avoid that, if there's an easier way?
