PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found

Article translations Article translations
Article ID: 322746 - View products that this article applies to.
This article was previously published under Q322746
Expand all | Collapse all

On This Page

SYMPTOMS

Data Transformation Services (DTS) packages that are scheduled as a job may fail and you receive one of these error messages:
The system could not find the environment option that was entered.
-or-

The system cannot find the file specified
With Microsoft SQL Server 2000 Service Pack 2 (SP2), you may see an error message similar to:
The process could not be created for step 1 of job 0xE080989ED67CC54E9931C07DC3282FDA (reason: Overlapped I/O operation is in progress) The step failed.

CAUSE

In any of the preceding cases, the job fails because the correct path for Dtsrun.exe is not found by SQLAgent.

WORKAROUND

To work around this problem, specify the full path for Dtsrun.exe in the job step. Follow these steps to change the DTSRun path by using SQL Server Enterprise Manager:
  1. Expand the server that is running SQL Server in SQL Server Enterprise Manager.
  2. Expand Management, expand SQL Server Agent, and then expand Jobs.
  3. Right-click the failed Job, and then click Properties.
  4. Click the Steps tab, and then click Edit.
  5. Modify the run line to include the full path to DTSRun.exe. If the path contains a space, you have to use quotes (" ") for the path.
For example:
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /~E080989ED67CC54E9931C07DC3282FDA
				
You can also work around the issue by using these steps:

  1. Make sure that the startup account for the MSSQLServer and SQLServerAgent service is part of the local administrators group. Add the account to the local administrator group if the account is not already in the group.
  2. Verify that the following registry key exists and that it shows the correct path. Also check the value and permissions for the key:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment
    This is where the system path is stored. If the value is null, you can copy the value of the following key to the preceding key:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Control\Session Manager\Environment
  3. Check the actual value of the system PATH environment variable. Remove any characters that are not valid or that are extra characters, such as a backslash. Make sure that the %SystemRoot%\System32;%SystemRoot%; entries are listed in the beginning. Verify if ...\MICROSOFT SQL SERVER\80\TOOLS\BINN is also included in the path. For SQL Server 7.0, the path is C:\MSSQL7\BINN.
  4. See if there are multiple versions of the Dtsrun.exe file on the computer. There may be multiple versions of Microsoft SQL Server or multiple instances on the computer. In this case, specify the full path for the correct Dtsrun.exe file. You can also try to adjust the system path variable so that the SQL Server 2000 directories are listed before the SQL Server 7.0 directories.
  5. From Query Analyzer, run the following command:

          xp_cmdshell 'path'
    						
    If it returns NULL, SQL Server is not reading the system path. After you check all of the preceding steps, if the issue is still not resolved, stop and then restart SQL Server to reload the system path variable.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
269074 INF: How to Run a DTS Package as a Scheduled Job

Properties

Article ID: 322746 - Last Review: October 16, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB322746

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com