I was really stumped when creating a DTS package today. I really couldn’t figure out how to loop the tasks. The package failed when I created a looping workflow. I found one alternative with using the Execute SQL Task, but it wasn’t really quite the solution I had in mind. After some googling I found this great article from SQLDTS.com. It explains how to loop back to step 1:
Option Explicit
Function Main()
Dim pkg, stpbegin
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSExecuteSQLTask_1")
'The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
End Function