• 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
else
  ***** 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?

Thanks

Rog
0
Rog D
Asked:
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
0
 
Rog DManager Inforamtion SystemsAuthor Commented:
Matthew,

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.

Rog
0
 
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
Else
      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.
0
 
Rog DManager Inforamtion SystemsAuthor Commented:
I understand, What exactly does the  DTSStepScriptResult_DontExecuteTask
do.  

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.

Thanks,

Rog
0
 
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?<<
Yes.

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

>>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.
0
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