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 DSQL Developer / Web Development / Business AnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 DSQL Developer / Web Development / Business AnalysisAuthor 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rog DSQL Developer / Web Development / Business AnalysisAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.