DTS package not running as scheduled job

[mutley1]

Hi all,

Not sure if this is a DTS or VB question so apologies if it is in the wrong forum.

i have a package that is running fine on 1 server using the following VB. It creates a text file and sends the results out.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 
Function Main()
 
On Error Resume Next
 
'Set the report output directory (end with a backslash) dir = "D:\TrimServicesAnalysis\"
'Set the text report file name
filename = "WorkgroupServiceStatus.txt"
 

'open the file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
set WSHShell = wscript.createObject("wscript.shell")
'open the data file
Set oTextStream =
oFSO.OpenTextFile("D:\TrimServicesAnalysis\LISTWorkgroup.txt")
'make an array from the data file
RemotePC = Split(oTextStream.ReadAll, vbNewLine) 'close the data file oTextStream.Close
 
For Each strComputer In RemotePC
 
Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colRunningServices = objWMIService.ExecQuery _
    ("Select * from Win32_Service")
    For Each objService in colRunningServices
        If ucase (objService.DisplayName) = "TRIM CONTEXT WORKGROUP SERVER"
Then
        Report = Report & vbCrLf & "Computer " & strComputer & " reports service " & objService.DisplayName  & " is " & objService.State 
        End If
    Next
Next
 
If Not oFSO.FolderExists(dir) Then
    oFSO.CreateFolder(dir)
End If
Set ts = oFSO.CreateTextFile (dir & filename, True) ts.write report
 
 
 
 Main = DTSTaskExecResult_Success
End Function

I have saved the DTS package to another server, scheduled it but it sends empty text files (I have also recreated the directory where it reads text files from etc.). If I run it from DTS designer, it works fine and sends files with data in it, but the scheduled job sends blank files. All SQL accounts are the same on both servers.

Any ideas welcomed.

Thanks.

M.

 

[mutley1]

I've just created a batch file and that also runs the package fine when run manually, but when I use windows scheduler, it sends the blank files again. Anyone come across this before as I'm pulling my hair out now!!

Thanks,

M.

 

[unclerico]

and then this to the end of the VBScript:

If Err.Count = 0 Then
  ts.Close
  Set oFSO = Nothing

  Main = DTSTaskExecResult_Success
Else
  ts.WriteLine Err.Description
  ts.Close
  Set oFSO = Nothing

  Main = DTSTaskExecResult_Failure
End If

Add an On Failure workflow to the DTS Package, point it to another ActiveX Task and create a text file that tells you that the process failed. If you get an error reported in the output file then it has got to be a permissions issue.

 

[mutley1]

Thanks unclerico,

I found the issue now - it was dcom that needed a bit of configuration tweaking.

Thanks again,

M.