You can use a parent or master package to control the execution of one
or more child packages. This is often used in a looping scenario, or
when there are a number of individual packages that make up a process.
This can be achieved very simply in SQL Server 2000 by using the Execute
Package Task. As well as being very simple to use it supports
transactions, the benefits of which should be obvious.
The alternative is to use an ActiveX Script Task to achieve the same
thing, but without support for transactions. Whilst the Execute Package
Task has the ability to pass global variable values between the parent
and child package, this method has the ability to manipulate the entire
package. The sample demonstrates this, first reading a global variable
value from the parent and assigning it to a global variable in the child,
and secondly by setting the DataSource property of a connection in the child
package. Another benefit of the ActiveX Script method is that you can pass values
from the child back to the parent, for which there is no equivalent in the
Execute Package Task. This is also demonstrated in the sample code.
Below is a simple example of how to load and execute a local server
package:
' 205 (ExecPkgFromPkg)
Option Explicit
Function Main()
Main = DTSTaskExecResult_Success
Dim oPkg, oStep
Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName, sErrMsg
Set oPkg = CreateObject("DTS.Package")
' Assign parameters
sServer = "(local)"
sUID = ""
sPWD = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName = "MyChildPackage"
' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName
' You can amend the child package here, e.g.
' Example 1 - Change a global variable value, and set it to a parent value
oPkg.GlobalVariables("ChildVar").Value = DTSGlobalVariables("ParentVar").Value
' Example 2 - Change a connection directly
oPkg.Connections("Text File").DataSource = "C:\StaticFilename.txt"
' Execute the Package
oPkg.Execute
' Pass a child value or variable back to the parent, after execution e.g.
DTSGlobalVariables("ParentVar").Value = oPkg.GlobalVariables("ChildVar").Value
' Now check for errors in the Child Package
For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
End If
Next
' Clean Up
oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing
End Function
..