Run a DTS package multiple times based off a recordset.

Have you ever wondered how to run a SQL DTS package multiple times based off a recordset formed from a SELECT statement?

To accomplish this you will need to create at least two DTS packages.  The first package will be used to create the recordset and then will call the second package for each record in the recordset.  We will call the first package the Init package and the second package the Action package.

In the Init package we will need to create at least one global variable with the type of "dispatch", we will name this variable glb_rsInit.  We will use this variable to store the recordset.  Next we will need to setup a connection to the database we want to use for the recordset.  Next create a Execute SQL Task.  We will use this task to populate the glb_rsInit global variable.  When creating this task name it something appropriate (task_init), select the database connection and then enter the appropriate sql statement.  You can either use a SELECT statement or EXECUTE stored procedure statement, with something that will return a recordset.  Next click the parameters button.  Next click the output parameters tab.  Next select the rowset radio button.  In the dropdown box select the global variable we setup earlier (glb_rsInit).  This will place the output recordset to the glb_rsInit variable.  We will next need to create an ActiveX Script Task (name it task_loop).  The following is an example of the script.

'**********************************************************************

'  Visual Basic ActiveX Script

'************************************************************************

Function Main()

                Dim objPackage, rsInit, sServer, sPackage, iComplete, iStep, iRetry

                sServer = "servername"

                sPackage = "action_package_name"

                SET rsInit = DTSGlobalVariables("glb_rsInit").Value

                iRetry = 0

                WHILE Not rsInit.EOF

                                Set objPackage = CreateObject("DTS.Package")

                                objPackage.LoadFromSQLServer sServer,"username","password",,,,,sPackage

                                objPackage.GlobalVariables.Item("glb_ActionVariable").Value = rsInit.Fields(0).Value

                                objPackage.Execute

                                iComplete = 1

                                For iStep = 1 to objPackage.Steps.Count

                                                If objPackage.Steps(iStep).ExecutionResult = DTSStepExecResult_Failure Then

                                                                iComplete = 0

                                                End if

                                Next

                                objPackage.Uninitialize()

                                Set objPackage = nothing

                                If iComplete = 1 Or iRetry > 9 Then

                                                iRetry = 0

                                                rsInit.MoveNext

                                Else

                                                iRetry = iRetry + 1

                                End if

                WEND

                Main = DTSTaskExecResult_Success

End Function

The script declares the variables, sets the server name and action package in the first three lines.  The next line gets the recordset from the glb_rsInit variable.  The next line initializes the iRetry variable to 0, this variable is used to allow for the Action package to fail and retry it up to 9 times before we move to the next record in the recordset.  We now start the loop section of the code.  In this section we will setup a reference to the Action package, set a global variable (there could be multiple global variables) a value(s) from the rsInit recordset and execute the package.  We also check to see if the action package was successful or failed.  If it failed then we increment the iRetry variable and try executing the package up to 9 times in this example.  Once either the package executes or fails 9 times, we move to the next record in the recordset and execute the action package again with the new passed variable.  This will repeat until the end of the recordset is reached.

The action package in this example doesn’t really matter what it is programmed to do.  You would need to make sure that the global variable(s) are setup and named the same as you have setup in the Init package.  A simple example would be to have the action package write the passed variable to a test table.

After you have the Init package executing the Action package successfully, you can now setup a job to run the Init package on a given schedule.