Execute Package Task
The Execute Package task extends the enterprise capabilities of Data Transformation Services (DTS) by letting you run other DTS packages as part of a workflow. This capability is useful when:
- Package workflow is complex and can be broken down into two or more modular packages. For example, if you are loading a star schema, you can build a set of packages so that different packages are associated with filling each dimension table and the fact table. Such a strategy increases package readability and simplifies debugging because the individual packages are smaller and each package workflow is more focused. It also provides a higher level of security because authors may not require access to all packages.
- Units of work can be encapsulated into separate packages and joined as transactional components to the workflow of a master package. The master package runs the accessory packages and, based on the success or failure of the accessory packages, either commits or rolls back the transaction.
- Parts of a package workflow can be reused by other packages. For example, you can build a data extraction module that can be called from different packages. Each of the packages calling the extraction module performs different data scrubbing, filtering, or aggregation operations.
Global Variables and the Execute Package Task
You can use the Execute Package task to dynamically assign the values of global variables from a parent package to a child package. The child can process the global variable data passed to it in any Microsoft® ActiveX® script in its executable workflow.
Using global variables to pass information from one package to another is useful when parts of a larger workflow are assigned to different packages. For example, one package could download data on a nightly basis, summarize the data, assign summary data values to global variables, and pass the values to another package for further processing of the data.
Global variable values passed to the child package are handled according to their scope definitions described in the child package. Global variables defined as static in the child package retain their last assigned values, and global variables not defined as static return to their initial values after the script runs.
Transactions and the Execute Package Task
Joining Execute Package tasks to package transactions lets you join the execution of entire packages and other workflow steps into units of work that can be committed or rolled back. Before joining an Execute Package task to a transaction, consider the following:
- Only one transaction can be run at a time from a package.
- The Microsoft Distributed Transaction Coordinator (MS DTC) must be operational and running on the server in order for transactions to work. Also, the MS DTC client must be running on each computer on which a package runs for transaction joining to work.
The transactional context within which an Execute Package task runs can range from the execution context of the entire master package to specific parts of the workflow, and to the Execute Package task itself, if the task is not joined to any transactions.
When joining the Execute Package task to a transaction in DTS Designer, use:
- The Advanced tab in the DTS Package Properties dialog box to enable the use of transactions in a package and set general transaction characteristics, such as isolation level.
- The Options tab in the Workflow Properties dialog box to join the step associated with the Execute Package task to a transaction.
- The Insert Commit Size check box in the Advanced tab of the Data Transformation Properties dialog box to control the number of rows of data moved prior to committing a transaction. This option is available only when the Use fast load check box is selected).
Caution Creating workflows or ActiveX scripts in which an Execute Package task is used to call its own package can generate a stack overflow and cause Microsoft Management Console (MMC) to shut down. Generally, recursive operations are not recommended with the Execute Package task. If you need to use the Execute Package task recursively, make sure you set a global variable in an ActiveX script to monitor the nesting depth (for example, in a step ActiveX script or in an ActiveX Script task that determines precedence), and terminate the recursion in the script code before a specified depth is reached. The global variable must be passed to the subpackage through its GlobalVariable collection.
To add a DTS task to a DTS package