asked on

DTS question

i wrote an activex script , and i am getting errors, what am i doing wrong:

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************

Function Main()
Dim oFSO, oFolder


      set oFSO = createobject("scripting.filesystemobject")
      sPath = "C:\scripts\import"
      set oFolder = oFSO.GetFolder(sPath)

      msgbox oFolder.Path & " has " _
      & oFolder.Files.Count & " files and " _
     & oFolder.SubFolders.Count & " folders" _
     ,vbInformation+vbSystemModal _
       ,"Folder content count"

If (oFolder.Files.Count > 0) Then

            Main = DTSStepScriptResult_ExecuteTask
Else

      Main = DTSStepScriptResult_DontExecuteTask
End If

Set oFSO = Nothing

End Function

I want to go to the next task when file.count is >0...if i do ' Main = DTSTaskExecResult_Failure it will just fail totally

bwdowhan

Try this:

Function Main()
Dim oFSO, oFolder

set oFSO = createobject("scripting.filesystemobject")
sPath = "C:\scripts\import"
set oFolder = oFSO.GetFolder(sPath)

msgbox oFolder.Path & " has " _
& oFolder.Files.Count & " files and " _
& oFolder.SubFolders.Count & " folders" _
,vbInformation+vbSystemModal _
,"Folder content count"

If (oFolder.Files.Count > 0) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

Set oFSO = Nothing

End Function

From the Active-X script, you can use workflow properties to define which step to go to on success and what to do on failure (maybe nothing)...

lytung818

ASKER

when it goes on failure it means that i get an error message. is there a way to supress thesse messsages? if i do a scheduled dts package, will these messages pop up?

lytung818

ASKER

also i have this error handler

On error go to ErrHandler

code here---

ErrHandler:

Main = DTSTaskExecResult_Failure

Resume Exit Sub

i get an expected statement error. what am i doing wrong?

Anthony Perkins

>>is there a way to supress thesse messsages?<<
You need to remove all MsgBox and any other interactive function (such as InputBox) from Scheduled DTS Packages.

>>i get an expected statement error. what am i doing wrong?<<
You are using VB syntax in VB Script. There is no "On error go to ErrHandler" (as an aside that syntax is not even correct in VB) and there is no "Resume". The best you can do in VBScript is:

On Error Resume Next

lytung818

ASKER

1. thanks how do i remove the msgbox from scheduled DTS package?

2. okay instead of doingthis:

If (oFolder.Files.Count > 0) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

is there a way to prevent goingto the next step when the count is < 0?

lytung818

ASKER

okay i figured out #2. I disabled the next step. Ignore that question

now aboutthe error handling. I dont want to do On Error Resume Next. On any error i want to alert me by email but i can't seem to be able to do:

On error
Main = DTSTaskExecResult_Failure

ASKER CERTIFIED SOLUTION

bwdowhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial