Technical Article

Mark DTS Task As Complete


This is a VBScript that can be placed in an ActiveX Script Task.  The purpose of this script is to mark other tasks in the DTS package as complete before they execute.  In this way you can ensure a task does not execute.

The Dim and Set lines give you access to the objects in the DTS package.

The next line (starting with DTSPackageObject.Steps) is the line that marks a specific task as complete.

To make this script work for you, you must change everything contained within between the double quotes with the name of the task you want to mark as complete.  To find it right click a task or data transformation arrow and select Workflow.  Now click the Options tab.  The highlighted name is the name you need to put between the double quotes.

One thing to keep in mind is that SQL Server counts tasks marked completed by the above code as failed so if a job executes a DTS package and a VBScript marks a task as completed the job will complete with failure.

I want to thank the authors of the book SQL Server 2000 DTS for showing me the code to mark a task as completed in their book.

'  Visual Basic ActiveX Script

Function Main()

    Dim DTSPackageObject

    Set DTSPackageObject = DTSGlobalVariables.Parent

    DTSPackageObject.Steps("DTSStep_DTSActiveScriptTask_11").ExecutionStatus = DTSStepExecStat_Completed

    Main = DTSTaskExecResult_Success
End Function


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating