Unable to Resolve Service Name, Oracle connection from ASP.NET or SSIS Business Intelligence Studio
Problem
In one of my recent projects, I had this peculiar problem where I was not able to connect to Oracle Database from my ASP.NET application and every time it gave me an error like "Unable to resolve service name", although my tnsnames.ora file was correct and moreover, I was able to connect to the same oracle datasource from the Oracle SQL Developer studio, it was just driving me crazy.
Solution
One quick solution I figured out for both my connecting to oracle from the BIDS and Microsoft Visual Studio web configuration like below
Lets say your tnsnames.ora reads like below
oradb.mydomain.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host.mydomain.com)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb.mydomain.com)
(FAILOVER_MODE =
(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
)
)
)
This is what has to be done when putting the connection string inside Visual Studio to get it connected to the oracle database.
<connectionStrings>
<add name="myConn" connectionString="Server=host.mydomain.com/oradb.mydomain.com; User Id=guest; Password=guest" providerName="System.Data.OracleClient" />
So above all that I did was to mention the host name along with the service name. Mind you, there is one important thing here, the oracle data source is running on the default port 1521.
The problem associated with connecting to Oracle datasource using an OLEDBDatsource control on BIDS is slightly more than just the connection to the oracle datasource if you are working on a 64 bit machine/PC. The BIDS is a 32 bit application and so you have to make sure that you have a 32 bit oracle client installed on your PC along with the 64 client of Oracle otherwise the BIDS may not be able to detect the "Oracle Provider for OLE DB" driver from the PC that you are working on.
Once this is resolved, like the visual studio, here also we will mention the DataSource in the connection string as host/service_name in the connection used as shown in the screen shot shown below.
In the connection manager above, the Server or file name is mentioned as "host.mydomain.com/oradb.mydomain.com". But here also please note that in my case the oracle datasource was running on default port 1521.
Thants it ! Happy coding !
