The final task that you will learn to use in this chapter is the Execute Package task. The Execute Package task enables you to call another package (called a subpackage) from within a package, pass parameters to the subpackage, and wait until the subpackage completes before continuing with additional tasks in the calling package.
Use the Execute Package task to modularize your data movement application. You can execute packages as a group , in a defined order, or using a common set of global variables. You can use specialized packages for particular functionality (such as error control). You can use a single package to begin the execution of the entire data movement application (called a master package) and initialize all variables used for a particular execution of the entire application.
The Execute Package task executes in the same process space as the calling task (which enables it to use the same global variables values as the master package). If you call multiple Execute Packages simultaneously , they execute in parallel. The number of tasks that execute in parallel is one of the package properties of the calling package. By default, the number of tasks in a package that can execute in parallel is limited to four. If you parallelize the data movement application, and the computer on which the application is executing has sufficient processing power to handle the execution of additional tasks in parallel, you should increase the number of tasks that can execute in parallel in the master package. In Chapter 5, you will learn how to set the number of tasks that a package will execute in parallel.
To allow your packages to scale this way, you must design this type of parallelism into the data movement application. Remember also that you must use separate connection objects for each task that you want to run in parallel. Using multiple packages, each with their own connection objects, helps you parallelize a data movement application.
In the following procedure, you will create a package that containing two Execute Package tasks. These execution tasks will call the UpdateProductDim and the UpdateCustomerDim packages. These packages will become the primary packages in the data movement application you are building that will periodically update each of the dimension tables used by the Sales cube and then update the fact table used by the Sales cube. Although these two packages do not yet accomplish all the tasks necessary for the data movement application, this master package will provide a starting place for configuring the entire application.
In the SQL Server Enterprise Manager console tree for your default instance, right-click Data Transformation Services, and then click New Package.
On the Task menu, click Execute Package Task, which prompts you to provide the package name , location, and connection information.
Type Call UpdateProductDim Subpackage in the Description box, click Structured Storage File in the Location list, and then type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\ UpdateProductDim.dts in the File Name box.
Click the ellipsis to the right of the Package Name box, double-click UpdateProductDim, and then type mypassword in the Password box.
The package ID for this subpackage is retrieved and will be saved as part of this Execute Package task. Because the package ID is used, this Execute Package task will always execute the most recent version of the UpdateProductDim package. However, if you change the package ID of this package, this Execute Package task will fail.
The Execute Package Task Properties dialog box includes two additional tabs: the Inner Package Global Variables tab and the Outer Package Global Variables tab. You can use these tabs to pass global variable values to the subpackage. You will incorporate this functionality into the data movement application in Chapter 6 and Chapter 7.
A package called by an Execute Package task can process global variable data passed to it in any ActiveX script in its executable workflow. Inner package global variables enable you to set values for global variables that are defined in the package being called. Outer package global variables enable you to define which of the global variable values defined in the calling package will be passed to the package being called.
Click OK to save this Execute Package task and then click Execute Package Task on the Task menu.
Type Call UpdateCustomerDim Subpackage in the Description box, click Structured Storage File in the Location list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\UpdateCustomerDim.dts in the File Name box, type UpdateCustomerDim in the Package Name box, type mypassword in the Password box, and then click OK.
You have successfully created a simple master package that calls two subpackages. Notice that because this package does not contain precedence constraints, both tasks will execute simultaneously as soon as the package is executed.
On the toolbar, click Save.
Type MasterUpdate in the Package Name box, type mypassword in the Owner Password box, click Structured Storage File in the Location list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\ MasterUpdate.dts in the File Name box, and then click OK.
Type mypassword , click OK, and then click OK again.
Close the MasterUpdate package in DTS Designer and then close SQL Server Enterprise Manager.
You have successfully created the master package for this prototype of a data movement application. In subsequent chapters, you will add functionality to this package. For example, in Chapter 6 you will configure global variables in the MasterUpdate package that will be passed to each subpackage, and in Chapter 7 you will add some initialization steps that will populate global variable values from a table in the SBS_OLAP database before these Execute Package tasks execute. In addition, in Chapter 9 you will add a task that updates the SalesFact table in the SBS_OLAP database after the UpdateProductDim and UpdateCustomerDim packages update the appropriate dimension tables.