Good Old Connectivity Issue with Oracle in x64 Environment
When you try to develop an SSIS package in x64 Environment in BIDS that connects to a Oracle Database, you will invariabely run into connectivity issues if the installed Oracle Client tools version is 10.2.0.1 (x86). You will obviously need to install the 32-BIT version of Oracle Client to communicate with BIDS which is a 32-BIT application !
Subject:
ORA-12154 or ORA-6413 Running 32-bit Oracle Software on 64-bit Windows OS.
Applies to:
Oracle Net Services - Version: 8.1.7.0 to 10.2.0.2
Oracle Data Provider for .NET - Version: 8.1.7.0 to 10.2.0.2
Oracle Objects for OLE - Version: 8.1.7.0 to 10.2.0.2
Oracle Provider for OLE DB - Version: 8.1.7.0 to 10.2.0.2
Oracle ODBC Driver - Version: 8.1.7.0 to 10.2.0.2
Microsoft Windows XP (64-bit Itanium)
Microsoft Windows Server 2003 (64-bit Itanium)
Microsoft Windows XP (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)
Symptoms:
You are attempting to connect to the Oracle database from a Windows platform using one of the following programmatic interfaces
· ODBC
· OLEDB
· OO4O
· ODP.NET
After installing 32-bit Oracle client software on a 64-bit Windows operating system (OS) you receive one of the following errors:
ORA-12154: TNS:could not resolve the connect identifier specified
or
ORA-6413: Connection not open.
Cause:
64-bit Microsoft OS's install 32-bit applications into the following location
"C:\Program Files (x86)\..."
rather than the typical location of
"C:\Program Files\..."
This causes an existing networking bug to occur where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.
The following bug has been filed to correct this behavior:
Bug 3807408 < https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3807408 >
NOTE: You will be able to view this bug details in the Metalinks site only if you have a Metalink login ID which comes with a support agreement with Oracle
Additional Information:
The reason you receive an ORA-12154 vs. an ORA-6413 is generally due to which programmatic interface you have chosen to use to connect to Oracle.
The ORA-12154 is the typical error seen when connecting with up-to-date interfaces using the latest version of the Oracle Call Interface (OCI):
· Oracle ODBC Driver
· Oracle Provider for OLE DB
· Oracle Objects for OLE
· Oracle Data Provider for .NET (ODP.NET)
· Microsoft's .NET Managed Provider for Oracle
The ORA-6413 is typical of using older interfaces which make legacy API calls such as Oracle's OCI Version 7 API:
· Microsoft ODBC Driver for Oracle
· Microsoft OLE DB Provider for Oracle
Solution:
To resolve this problem try the following solution:
The Best possible solution is to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0)
rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error. If it is absolutely necessary to stick to the 10G version of the client (it shouldn't be), refer to the following:
For 32-BIT Oracle Client:
1. Install 10.2.0.2 upgrade - You need to select the correct Oracle Home path of previous 10.2.0.1 install
This install might fail at copying gacutil.exe...
To Fix this search for gacutil* in C drive and you will find gacutil.exe anc gacutil.exe.config files in some directory
Copy them to C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin
Some times you can't find gacutil.exe then copy this file from some other server
2. Rerun the previous install after copying gacutil files - Install will be successful, After that you need to install a patch(5383042) for 10.2.0.2
3. To install the patch for 10.2.0.2:
Create folder Patches in D:\oracle\product\10.2.0\client_1
Copy folder 5383042 from D:\Oracle32bit\Patchfor10202\p5383042_10202_WINNT TO D:\oracle\product\10.2.0\client_1\Patches
Go to command prompt
cd D:\oracle\product\10.2.0\client_1
set oracle_home=D:\oracle\product\10.2.0\client_1
cd D:\oracle\product\10.2.0\client_1\Patches\5383042>
D:\oracle\product\10.2.0\client_1\OPatch\opatch apply
The above command calls opatch.bat and you are in the patch folder so "apply" will apply the current patch folder you are in
It will prompt you for y/n ...say y
Create folder Patches in D:\oracle\product\10.2.0\client_1
Copy folder 5383042 from D:\Oracle32bit\Patchfor10202\p5383042_10202_WINNT TO D:\oracle\product\10.2.0\client_1\Patches
Go to command prompt
cd D:\oracle\product\10.2.0\client_1
set oracle_home=D:\oracle\product\10.2.0\client_1
cd D:\oracle\product\10.2.0\client_1\Patches\5383042>
D:\oracle\product\10.2.0\client_1\OPatch\opatch apply
The above command calls opatch.bat and you are in the patch folder so "apply" will apply the current patch folder you are in
It will prompt you for y/n ...say y
To check if the patch has been applied
D:\oracle\product\10.2.0\client_1\OPatch lsinventory
For 64-BIT Oracle Client:
1. Install 64bit 10.2.02 upgrade - Select the correct Oracle Home.
2. Install 64bit 10.2.03 upgrade - Select the correct Oracle Home.
3. Copy TNSNAMES folder from other servers to D drive.
4. Create an Environment variable TNS_ADMIN to path D:\TNSNAMES
However, It is strongly recommended to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0)
rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error.
NOTE: This will allow you to create and run the package successfully from BIDS, but not as a Sql Job (in a x64 version of Sql Server). The reason being x64 version of Sql Server will always invoke x64 version of the DTEXEC.exe to run a job which will cause it to fail. We can confirm that by running the job successfully from command line using the 32-BIT DTEXEC.exe. (Running it from the C:\Program Files (x86) directory). So, to workaround this problem, we can choose one of the following solutions:
Solution 1:
Run the Job under the cmdExec subsystem and modify the command line so that the 32-BIT DTEXEC.exe is invoked to execute the job.
Phase 1 Update Dec 12, 2009:
Note: In Sql Server 2008 onwards you can still run the Job under Integration Services subsystem and use 32-BIT DTEXEC.exe. You need to go to "Execution options" in the Job Step properties and check "Use 32-BIT Runtime"
Solution 2:
Install the 64-BIT Oracle Client tools side by side with the 32-BIT one (Make sure you either apply the patch if you install 10G version or STRONGLY recommended to use the 11G Release 1 version)
Solution 3:
Modify the following Registry key to run the job under the Integration Services subsystem but to invoke the 32-BIT DTEXEC.exe to execute the package:
HKLM->SOFTWARE->MICROSOFT->MSDTS->SETUP->DTSPATH. Change the value of the DEFAULT key to point to the 32-BIT DTEXEC.exe. Typically, under Default installation, that path will be C:\Program Files (x86)\Microsoft SQL Server\90\DTS\
NOTE: You will need to restart the Sql Server Agent Service for the registry change to take effect.
Cheers,
Debs !!