Job Running CmdExec Step Not Completing

  • I have created a DTS package that accepts two variables and outputs them to a text file:

    Function Main()
     Dim str1, str2
     Dim fso, MyFile

     str1 = DTSGlobalVariables("String1").value
     str2 = DTSGlobalVariables("String2").value

     Set fso = CreateObject("Scripting.FileSystemObject")
     Set MyFile = fso.CreateTextFile("c:\testfile.txt", True)
     MyFile.WriteLine(str1 & " " & str2)
     MyFile.Close

     Main = DTSTaskExecResult_Success
    End Function

    The DTS is called by a job; step as follows:

    "C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "Test" /W "0" /E /A String1:8="David" /A String2:8="David" 

    The job's owner has Windows admin rights; SQL Server is running in mixed authentication mode.

    When I run the DTS manually the file is created as expected however, when I run the DTS using the job the status remains at Executing Job Step 1 and never completes. Can anyone help expalin why this is happening please? I have checked the logs and Windows Event Viewer but there are no errors being logged. As a test, I also created a simple DTS package that displayed a message box. Again, when the DTS was run manually the message box appeared but when run using a job the job never completed and the message box never displayed.

     

  • Check who is executing.

    When you execute DTS package then it is your SQL Login who is executing the job. If you are an admin in SQL Server (for example your server is registered in EM with SA credentials and SQL Server  is started on LocalSystem account then you are running as a Windows administrator who has rights to create files. If you are running DTSRUN.exe job manually then it is your Windows login who may or may not have WRITE access on the directory where the file is created. if you are running your job on schedule make sure you specified credentials that have appropriate rights.

     

     

    Regards,Yelena Varsha

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply