Stop DTS Package Execution Conditionally Without Failure

By | February 22, 2007

There is no straight way in DTS to stop the package on a certain condition without having it fail. Suppose in DTS package is suppose to be executed everyday at 8:00AM. This package is looking for a file c:\abc.txt. If the file has not arrived, it should gracefully stop without reporting any error. If the file has arrived then process the file and report success. This situation is handled by a bit of programming. Write down the following code in ActiveX Script Task.

Dim fso, file1
Dim pkg
SET pkg = DTSGlobalVariables.Parent

Set fso = CreateObject("Scripting.FileSystemObject")

IF (fso.FileExists("c:\abc.txt")) THEN
	Msgbox "File exist. Continue"
	pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=False

	Set fso= Nothing

ELSE
	Msgbox "Stop"
	pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=True 
END IF
set pkg=nothing
Main = DTSTaskExecResult_Success

What this does is disables the second step and hence the whole sequence of task is automatically disabled. so the Task1 will report success and DTS package execution will be completed without executing task 2 and onwards. You can download the dts package example and try to run it on your computer. It looks for file c:\abc.txt. If this file exists, it will run total 3 task with result SUCCESS. If the file is not found, it will run only Task1 with result SUCCESS.

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

11 thoughts on “Stop DTS Package Execution Conditionally Without Failure

Leave a Reply

Your email address will not be published. Required fields are marked *