Execute a package from a package

By Darren Green

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