How to use an ActiveX script task to create a loop in DTS

Goal

1. We have a Transfer Data Task between two (2) SQL Servers.
2. We want this task to run 10 times through a loop.

Real World Example

A database administrator may need to create a testing environment with the destination database being much bigger than the source database, but based on the same type of data. A loop in DTS can be used to accomplish this.

Steps to Implement

1. In Enterprise Manager, open DTS designer.
2. Right-click on the white space of the DTS designer and choose Package Properties.
3. Go to the Global Variables tab and create a new global string variable called count, which will be used to hold the number of times this task will be looped.

4. Click on the symbol in the DTS designer’s connection list to set up the first connection to the source database in your SQL Server.
5. Click on the symbol in the DTS designer’s connection list to set up the second connection to the destination database in your SQL server.
6. Highlight the two SQL Server connections and click on the symbol in the DTS designer’s Tasks list so that it will create a Transfer Data Task from the source database to the destination SQL Server database.
7. From the Tasks list, click on the symbol to add the following ActiveX script task:

FunctionMain()

Dimpkg
Dimstpbegin

‘Increasethecountby1aftereachexecutionoftheTransferDataTask
DTSGlobalVariables("count").value=DTSGlobalVariables("count").value+1

‘decideifweneedtoloop
ifDTSGlobalVariables("count").value<11then

setpkg=DTSGlobalVariables.Parent
‘thenameofthetaskcanbeobtainedbyrightclickonthetask,gotoWorkflowProperties,then
‘choosetheoptionstab.
setstpbegin=pkg.Steps("DTSStep_DTSDataPumpTask_1")
stpbegin.ExecutionStatus=DTSStepExecStat_Waiting

endif

Main=DTSTaskExecResult_Success

EndFunction

8. Highlight the ActiveX script task and the second SQL server connection; go to the Workflow in the menu and choose “On success” so that this ActiveX script task will be executed before the Transfer Data Task. Here is how it should look: