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

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: