Set cus = tsk.CustomTask
With cus
.Name = “tsk” & sBaseName
.Description = sBaseName
End With
pkg.Tasks.Add tsk
‘Create step for task
Set stp = pkg.Steps.New
With stp
.Name = “stp” & sBaseName
.Description = sBaseName
.TaskName = tsk.Name
End With
pkg.Steps.Add stp
fctCreateDynamicPropertiesTask = stp.Name
Set tsk = Nothing
Set cus = Nothing
Set stp = Nothing
ProcExit:
Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description
fctCreateDynamicPropertiesTask = “”
GoTo ProcExit
End Function
Conclusion
The Dynamic Properties task improves the level of control within a DTS package. The next
two chapters describe the two tasks that give you control between packages—the Execute
Package task and the Message Queue task.
Control Tasks
P

IV
378
The Execute Package task lets you execute one DTS package from another.
When to Use the Execute Package Task
Here are some reasons you might want to divide your DTS functionality between several
packages:
• The packages have become too complex. They will be easier to understand if the tasks
are divided between several packages.
•You have a task or group of tasks that performs a utility function. This functionality can
be put into a separate package so that the same code can be used by several packages.
• Some of the DTS functionality needs to have a higher level of security. You can put that
functionality into a separate package where it can be executed but not viewed or edited.
There are several ways to execute one package from another, in addition to using the Execute
Package task. This task is the easiest way to execute another package, but some of the other
ways have additional functionality. Here are the other possibilities:
•Use DTSRun from an Execute Process task. If you use an encrypted command line, you
can hide all the parameters used in the execution, including the name of the server, pack-
age, and user.
•Use the DTS object model from an ActiveX Script task. You can change the properties of
the package and its tasks before executing the package. You can read the values of global
variables after executing the package.
•Use OpenRowset to query a package from an Execute SQL task. You can return a record-
set from the child package when you use this strategy.
•Use the OLE Automation stored procedures from an Execute SQL task. This strategy
allows you to execute a package from the context of a remote server. You can also mod-
ify the properties before executing and read the values of global variables after executing,
as when you use the object model from an ActiveX Script task.
You can set the values of global variables in the child package when you use the Execute
Package task and in all but one of the other strategies. Using OpenRowset from an Execute
SQL task is the only method that does not allow you to set the values of global variables in the

XECUTE
P
ACKAGE
T
ASK
379
F
IGURE
18.1
You choose package and connection information on the General tab of the Execute Package Task Properties dialog.
23 0672320118 CH18 11/13/00 5:04 PM Page 379
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Most of the properties of the Execute Package task are concerned with package and connection
information. You set these properties on the General tab of the Execute Package Task
Properties dialog:
•
Description
—The description of the task.
•
FileName
—The file from which the DTS package is to be loaded. When the DTS pack-
age is not stored in a file, this property should be an empty string.
•
UseRepository
—If
FileName
is an empty string and this property is
TRUE
,the package is
loaded from the repository. If

— The server on which the DTS package is stored.
•
ServerUserName
,
ServerPassword
, and
UseTrustedConnection
—Connection informa-
tion for the server on which the DTS package is stored.
Setting Values of Global Variables in the Child
Package
You can pass information from the parent package to the child package by setting the values of
global variables. There are two ways you can do this:
•With fixed values, where you set the values of the global variables at design time in the
Execute Package task.
•With dynamic values, where the child package is sent the current values assigned to the
global variables as the parent package is being executed.
Control Tasks
P
ART
IV
380
23 0672320118 CH18 11/13/00 5:04 PM Page 380
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You send global variables with the fixed values method by adding global variables on the Inner
Package Global Variables tab of the Execute Package Task Properties dialog, as shown in
Figure 18.2. You can choose the name, the datatype, and the value for the global variable.
The Execute Package Task
C
HAPTER

23 0672320118 CH18 11/13/00 5:04 PM Page 381
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
F
IGURE
18.3
You choose existing global variables on the Outer Package Global Variables tab when you want to use the dynamic
values method.
Control Tasks
P
ART
IV
382
Global variable names are always case sensitive. If you are attempting to pass a vari-
able and you do not match the case of all the letters in the name, a new variable will
be created in the child package.
Also, a global variable passed to a child package overrules the option for required
explicit declaration of global variables. A new global variable will be created in the
child package when a global variable with the same name doesn’t exist, even if
explicit declaration of variables is required in that package.
C
AUTION
The Execute Package task has a
GlobalVariables
collection that contains a collection of
GlobalVariable objects. All of the fixed-value global variables that you pass to the child pack-
age are members of this collection.
The Execute Package task has an
InputGlobalVariableNames
property that contains a semi-
colon-delimited list of global variable names. These are the names of the dynamic value global

stp.DisableStep = True
End If
The Execute Package Task
C
HAPTER
18
18
T
HE
E
XECUTE
P
ACKAGE
T
ASK
383
It’s hard to find the right terminology to talk about these two kinds of global vari-
ables. I am not satisfied with the Inner Package/Outer Package labeling that the inter-
face gives to the two kinds of global variables, because both types become global
variables in the inner package.
I think it’s better to focus on the function of the global variables.
If you have fixed values that you always want to send to the child package, you cre-
ate global variables that use the fixed value method. You specify the fixed value,
along with the global variable name, on the Inner Package Global Variables tab.
If you have dynamic values that you want to set while your parent package is execut-
ing, you create global variables with the dynamic value method. You create global
variables in the parent package and send them to the child package on the Outer
Package Global Variables tab.
N
OTE

SQL Task
F
IGURE
18.4
The Remote Execution Utility contains the functionality to execute a Called Package on any server and report the
results to the Calling Package.
You can find the utility and a sample of a Calling Package and a Called Package in three files
on the CD—CallingPackage.dts, RemoteExecutionUtility.dts, and CalledPackage.dts. To use
these sample packages, you have to do the following:
1. Save the Remote Execution Utility and the Called Package to SQL Server. You can put
them on any servers you like, as long as you reference those servers appropriately in the
Execute Package task in the Calling Package.
2. The Calling Package has to be set to log to SQL Server. Check the logging settings in
the Package Properties dialog.
3. In the Execute Package task of the Calling Package, you must choose the Remote
Execution Utility as the package to be executed.
4. Set the global variables in the Execute Package task to appropriate values.
23 0672320118 CH18 11/13/00 5:04 PM Page 384
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You are only required to send one value to the Remote Execution Utility—the
PackageName
.
The utility will open the Called Package from SQL Server storage on the local computer using
integrated security. The utility will execute the Called Package in the context of the local
server, again using integrated security. You can override these default values by sending these
global variables to the Remote Execution Utility:
•
PackageStorageServer
—The name of the server where the Called Package is stored
using SQL Server storage.

also use the full lineage value, the package ID, the package version ID, or the package
name.
•
ReportName
—The name of the INI file used to report results. If not supplied, the name
of the INI file will be set to RemoteExecutionReport.ini.
•
ReportDirectory
—The directory where the INI file is to be written. The default value is
set in the Remote Execution Utility to the user’s temporary directory.
•
StepName
—The step associated with a Transform Data task for which you want a com-
plete report. If this is not set, no step will be given special reporting.
The Execute Package Task
C
HAPTER
18
18
T
HE
E
XECUTE
P
ACKAGE
T
ASK
385
The initial version of SQL Server 2000 limits the headings used for INI files in the
Dynamic Properties task to 255 characters. Because of this limitation, the Calling

INI file used in reporting. If an empty string has been chosen for these values, this task
disables the two reporting tasks.
•Find Results—A Dynamic Properties task that reads the values of the report INI file into
global variables.
• Report Results—An ActiveX Script task that displays a message box reporting on those
global variables. In a production environment, you could set this string as the message of
a Send Mail task, which could then be sent to the appropriate recipients.
The Calling Package doesn’t have to be this complicated, of course. If you use an unchanging
value for
CallingPackageID
, you don’t need the Set Calling package ID task. If you don’t
want to view the results in the package, you don’t need the Find Results and Report Results
tasks. The only thing you need to call the utility is a Dynamic Properties task.
23 0672320118 CH18 11/13/00 5:04 PM Page 386
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
F
IGURE
18.5
You only need a Dynamic Properties task to call the utility, but you may want to add other tasks to set global variables
and report results.
Creating the Task in Visual Basic
I have created a Visual Basic procedure,
fctCreateExecutePackageTask
,that creates a step, a
task, and a custom task for an Execute Package task. All the properties of the task can be set
with this procedure. The procedure does not let you add any members to the
GlobalVariables
collection. You can add dynamic value global variables with the
InputGlobalVariableNames
property. You can find the code for it in the directory for Chapter 18 on the book’s CD as a

Optional sBaseName As String = “ExecutePackageTask”, _
Optional sFileName As String = “”, _
Optional sInputGlobalVariableNames As String = “”, _
Optional sPackageID As String = “”, _
Optional sPackageName As String = “”, _
Optional sPackagePassword As String = “”, _
Optional sRepositoryDatabaseName As String = “”, _
Optional sServerName As String = “(local)”, _
Optional sServerPassword As String = “”, _
Optional sServerUserName As String = “”, _
Optional bUseRepository As Boolean = False, _
Optional sVersionID As String = “”) As String
On Error GoTo ProcErr
Dim stp As DTS.Step2
Dim tsk As DTS.Task
Dim cus As DTS.ExecutePackageTask
‘Check to see if the selected Base name is unique
sBaseName = fctFindUniqueBaseName(pkg, sBaseName)
‘Create task and custom task
Set tsk = pkg.Tasks.New(“DTSExecutePackageTask”)
Set cus = tsk.CustomTask
With cus
.Name = “tsk” & sBaseName
.Description = sBaseName
.FileName = sFileName
.InputGlobalVariableNames = sInputGlobalVariableNames
.PackageID = sPackageID
.PackageName = sPackageName
.PackagePassword = sPackagePassword
.RepositoryDatabaseName = sRepositoryDatabaseName

Set stp = Nothing
ProcExit:
Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description
fctCreateExecutePackageTask = “”
GoTo ProcExit
End Function
The Execute Package Task
C
HAPTER
18
18
T
HE
E
XECUTE
P
ACKAGE
T
ASK
389
L
ISTING
18.1
Continued
23 0672320118 CH18 11/13/00 5:04 PM Page 389
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Conclusion
The Execute Package task gives you a convenient way to execute one package from another,

You use the Message Queue task to coordinate the execution of two or more DTS packages.
One package has a Message Queue task configured for sending. When that task executes, it
adds a message to the specified queue. The other package has a Message Queue task config-
ured for receiving. When that task is executed, it looks for an appropriate message in the
queue. If it finds one, the task completes successfully. If it does not find the message, it contin-
ues checking until the message appears in the queue or the period of time specified by the
ReceiveMessageTimeout
property expires.
If you don’t have the MSMQ client installed on your computer, you will receive a
warning message when you attempt to create a Message Queue task. You are
allowed to create the task and set its properties. However, the task will not success-
fully execute unless you install the MSMQ client.
N
OTE
When to Use the Message Queue Task
Both the Message Queue task and the Execute Package task are used to coordinate the execu-
tion of two or more DTS packages. Here are the factors in deciding which one of these tasks
to use:
•Use the Message Queue task when you need asynchronous processing of two or more
packages. Use the Execute Package task to call one package from another in situations
where both packages are available at the same time. You can use the Message Queue task
in situations where one or more of the packages are executing on computers that are not
always connected to the network.
•Use the Dynamic Properties task when you need the tasks of two or more packages to be
joined in a transaction. The Message Queue task does not support transactions.
•You can use the Message Queue task to send string messages, global variables, or files
from one package to another. You can send only global variables with an Execute
Package task.
24 0672320118 CH19 11/13/00 5:00 PM Page 392
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

ESSAGE
Q
UEUE
T
ASK
393
F
IGURE
19.1
The Properties dialog of the Message Queue task when sending a message.
24 0672320118 CH19 11/13/00 5:00 PM Page 393
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The choice to send or receive messages is implemented with the custom task’s
TaskType
prop-
erty. This property uses the
DTSMQType
constants:
•0—
DTSMQType_Sender
•1—
DTSMQType_Receiver
You can also set the
Description
property for the task in the Message Queue Task Properties
dialog. The
Name
property is set automatically.
Setting the Queue Path
Whether you are sending or receiving messages, you have to enter the path of the queue that

DTSMQMessages
collection. The
DTSMQMessage
object has the
MessageType
property, which uses one of these
DTSMQMessageType
values:
•0—
DTSMQMessageType_String
•1—
DTSMQMessageType_DataFile
•2—
DTSMQMessageType_GlobalVariables
The
DTSMQMessage
object also has a set of properties, one of which is used for each of the
message types:
•
MessageString
—The text of the string message being sent.
•
MessageDataFile
—The path and filename of the data file being sent.
•
MessageGlobalVariables
—A semicolon-delimited list of the names of the global vari-
ables being sent:
‘NameVar1’;’NameVar2’;’NameVar3’;
The Message Queue Task

ReceiveMessageType
property of the
Message Queue custom task. This property uses the same
DTSMQMessageType
values that are
used by the
Message
object’s
MessageType
property.
24 0672320118 CH19 11/13/00 5:00 PM Page 395
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.