Recently one of my customers migrated and upgraded their servers from SQL 2000 32 bit to SQL 2005 64 bit following which their DTS packages stopped working. In this blog post I intend to explain the troubleshooting approach, we adopted to resolve the issue along with the resolution to the problem.
When you migrate DTS packages to SQL 2005 or above version of SQL Server, we have 2 options to run DTS packages either by using DTSRun.exe or by using DTExec.exe apart from running it through DTS Designer which is used for development.
Here are few things to take into consideration when migrating to 64 bit server.
- DTS Packages designed in 32 bit environment can only run with 32 bit run time and cannot be migrated to 64 bit.
- 32 bit runtime are not installed by default in the installation of the SQL Server and should be downloaded from the separate Feature pack
- In a 64bit SQL Server instance, SQL Agent is also 64 bit and hence it cannot execute 32 bit package directly using SSIS Job step type. So in order to execute the 32 bit package using 64 bit SQLAgent, you need to use Operating System CmdExec Job step which calls 32 bit DTSRun.exe or 32 bit Dtexec.exe
- If your 32 bit DTS Package is run by using 32 bit DTSRun.exe/Dtexec.exe, and if the DTS Package fetches the data from Oracle, you need to install 32 bit version of the Oracle client and not the 64 bit Oracle client.
From the Oracle side, we need to check the following
- The tnsping to the Oracle service should be working fine. If tnsping fails, you will have check and verify the tnsnames.ora,network,IP,port,firewall components and resolve that first before you proceed.
- From the Server, use command line sqlplus to log into the source oracle server and ensure the user & password is authenticated fine.
- In the default installation of Oracle client, SQLNet Authentication is set to NTS which means the authentication will be performed at windows level and if your SQLAgent account doesn’t have an access to the Oracle server, it will fail so in general whenever you are running DTS package which fetches data from remote Oracle server, you should turn off the Windows Authentication of Oracle and rely on the Oracle’s native authentication
To achieve this, you need modify the following parameters in SQLNet.ora
SQLNet.ora can be found in %ORACLE_HOME%\network\admin folder
Once we have the tested and verified above points, we should first run the DTS Package from the command prompt using DTSRun.exe or dtexec.exe and see if the package runs successfully. If you see any error here, it should be resolved first before scheduling the package to run as Operating System (CmdExec) Job step in SQLAgent Job.
Also by default the SQLAgent Job runs under the SQLAgent service account credentials unless proxy is configured. So ensure SQL Agent service account has all the necessary permissions to execute the DTS package, execute the Oracle binaries, load the Oracle dlls and load in the SQL Instance.
In our case, we were able to able to run the DTS Package using command line but when we schedule to run the DTS package as a Job, we received the following error in the Job History
[Microsoft] [ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed
The above error message is a generic ODBC provider error and searching it in the internet might result in many irrevelant hits.
However, since we had the package running fine under command prompt, we had an advantage in troubleshooting. So we decided to capture a procmon trace during a successful run from cmd and failed run from SQLAgent.
- Download and copy procmon on the server
- Capture procmon on the server, filtering “Process Name” Is “DTSRun.exe” OR “Dtexec.exe”
- Run the package from cmd prompt
- Save the procmon trace file.
- Capture procmon on the server, filtering “Process Name” Is “DTSRun.exe” OR “Dtexec.exe” AND “Process Name” Is “SQLAGENT90.exe” OR “SQLAGENT.exe”
- Run the SQLAgent Job which fails
- Save the procmon trace file.
Open the procmon from successful run and filter on “Path” contains “oracle” (this is because oracle home will have keyword oracle) and observe the files accessed from oracle client
Open the procmon from unsuccessful and filter on “Path” contains “oracle” and observe the files accessed from Oracle client
As clearly observed in the failed run, the DTSRun.exe cannot find oci.dll and hence it cannot locate the oracle home which causes it to fail. However in the cmd prompt run, it finds the oci.dll in correct oracle home and proceeds with the successful execution.
Our first guess is, is the ORACLE_HOME environment variable correctly set, & is it set correctly in regedit path as well HKLM\Software\Oracle or HKLM\Software\Wow6432mode\Oracle
However it was set correctly in our case, so what is going wrong here. If you observe procmon run of the failed instance, you will see that DTSRun.exe looks for oci.dll in various path but cannot locate it in any of the paths while in successful run it can locate it under correct path. So the path variable might be different in both the cases.
So to verify our hypothesis, we ran the SQLAgent Job with CMdExec Job step but with the following command
Path > “c:\path.txt”
We checked path.txt and found that path variable for SQLAgent service account did not had the path for oracle home %ORACLE_HOME%\bin due to which it was unable to locate oci.dll which explains the failure
To resolve the issue, we had to set the path environment variable for the user profile which runs sql agent service as PATH=%PATH%;%ORACLE_HOME% and restart the SQL Agent service.
Following the restart, the Job began to execute the DTS Package and it run happily ever after 🙂
If you like my posts, please consider sharing it viz Twitter,facebook,linkedin etc
Hope this helps !!!
Premier Field Engineer