• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1064
  • Last Modified:

SQL Package Active X Task (Stop Execution from within VBscript)

I have a SQL 2000 DTS package.

In side the package I have an Active X Task (VBScript).

It looks to see if a file exists.  

If Yes   Main =  DTSTaskExecResult_Success
  ***** want to stop procedure but return success to scheduled job.

In ohter words I have a package the is scheduled to run every day.  Some days we don't receive a file, so I don't want an error or failure, just to stop the pacage from running and exit successfully.

Is this possible?


Rog D
Rog D
  • 2
  • 2
1 Solution
Patrick MatthewsCommented:
Main =  DTSTaskExecResult_Success
Dim fso, result
Set fso = CreateObject("Scripting.FileSystemObject")
result = fso.FileExists("path to file here")
Set fso = Nothing
If Not result Then Exit Sub

'put rest of code here
Rog DManager Inforamtion SystemsAuthor Commented:

This is good and I do understandt this.  

The code you provided does check for a file, but then I want to exit DTS Package with success not just exit the task.

The reason for this is if a file is found I want to the dtspackage task with success and continue.

Anthony PerkinsCommented:
The way to handle this is with a Workflow ActiveX script on the task.  Something like this:

If file found then
      Main = DTSStepScriptResult_ExecuteTask
      Main = DTSStepScriptResult_DontExecuteTask
End If

In order to write code to the Workflow ActiveX script:
1. Right click on the task.
2. Select Workflow properties.
3. Click on Options tab
4. Click on "Use ActiveX Script"
5. Select Properties.

This code will execute BEFORE the task.
Rog DManager Inforamtion SystemsAuthor Commented:
I understand, What exactly does the  DTSStepScriptResult_DontExecuteTask

Will is stop the package from running?

Will is set the exit code to success?

The problem I have is I can set Main = "Success" or "Failure" based on finding a file.

Then take the workflow failure route, but then the job shows "Error"

Basically I want the job to end as a success if a file is NOT found.

If a file is found, I want the package to continue with success route executing other tasks or other items.


Anthony PerkinsCommented:
>>What exactly does the  DTSStepScriptResult_DontExecuteTask<<
They are the constants used in Workflow ActiveX Scripts in much the same way that DTSTaskExecResult_Success and DTSTaskExecResult_Failure are used in the Task ActiveX Script

>>Will is stop the package from running?<<

>>Will is set the exit code to success?<<

>>The problem I have is I can set Main = "Success" or "Failure" based on finding a file.<<
I understand.  It will never work that way.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now