September 26, 2006 at 2:15 pm
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.CloseMain = 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.
September 26, 2006 at 3:59 pm
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