Dynamically Changing Global Variable Values in Another Package

Global variable values can be updated at run time using a number of different mechanisms, for example, by using the DTSRun utility (see Chapter 3) or the Execute Package task. Using the Execute Package task enables a master package to communicate with subpackages and modify global variable values in them at run time.

The Execute Package task supports inner package and outer package global variables . Inner package global variables specify values for subpackage global variables. Outer package global variables specify the master package global variables that are passed to the subpackage. For a given execution, identically named global variables in the subpackage use the values of the global variables passed from the master package.

In the following procedures, you will learn how to use inner package and outer package global variables. First, you will modify the MasterUpdate package to use an inner package variable when executing the UpdateCustomerDim package. You will then modify the MasterUpdate package to use outer package global variables when executing both the UpdateCustomerDim and UpdateProductDim packages.

Configure an inner package global variable

1. In the SQL Server Enterprise Manager console tree, right-click Data Transformation Services in your local instance and then click Open .

2. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication and then double-click MasterUpdate.dts.

3. Double-click MasterUpdate in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

4. On the design sheet, double-click the Call UpdateCustomerDim Subpackage step, and then click the Inner Package Global Variables tab.

5. In the Variables window, select giMaxErrorCount in the Name list.

   The global variable type and current value are retrieved from the UpdateCustomerDim package.

   

6. Type in the Value box and then click OK.

7. On the toolbar, click Execute.

8. Click OK to acknowledge that one task failed during execution, and then double-click Call UpdateCustomerDim Subpackage in the Status window.

   The UpdateCustomerDim package did not transform any rows because the MaximumErrorCount property in this package was changed to 0 at run time by the MasterUpdate package, and the NewCustomers.txt source file contains two errors. As a result, the number of failing rows exceeded the maximum number permitted by the Transform Data task.

9. Click OK and then click Done.

You have successfully configured a master package to change the global variable value in a subpackage. However, when you use this approach, you must edit the master package each time you want to change this value. As you will learn in the following procedure, using outer package global variables solves this problem by enabling you to configure the global variable value passed to the subpackage at run time from outside the package.

Configure an outer package global variable

1. Verify that no tasks are selected on the design sheet, and then right- click an open area of the design sheet and click Package Properties.

   In this procedure, you will create the giMaxErrorCount and giBatchSize global variables in the MasterUpdate package that will be passed to each of the subpackages.

2. Click the Global Variables tab and then click New.

3. Type giMaxErrorCount in the Name box, select Integer (1 byte) in the Type list, and type 5 in the Value box.

4. In the next line in the Variables window, type giBatchSize in the Name box, select Integer (small) in the Type list, type 500 in the Value box, and then click OK.

   You have successfully created the global variables in the master package that match the global variables you want to manipulate in the UpdateCustomerDim and UpdateProductDim subpackages.

   

   Important  

   Since global variable names are case-sensitive, you must ensure that the name of the global variable in the master package and the name in each subpackage are identical, including case. If not, the global variable in the subpackage will not be updated.

5. On the design sheet, double-click the Call UpdateCustomerDim Subpackage step, and then click the Inner Package Global Variables tab.

6. Click giMaxErrorCount and click Delete.

7. Click the Outer Package Global Variables tab.

8. In the first row in the Variables window, select giMaxErrorCount in the Name list. In the second row, select giBatchSize in the Name list.

   

9. Click OK to save the modification of the Execute Package task.

   You have successfully configured the Call UpdateCustomerDim Subpackage step to pass global variable values to the UpdateCustomerDim package when it executes the subpackage. Next you will configure the Call UpdateProductDim Subpackage step to pass the same global variable values to the UpdateProductDim subpackage.

10. On the design sheet, double-click the Call UpdateProductDim Subpackage step, and then click the Outer Package Global Variables tab.

11. In the first row in the Variables window, select giMaxErrorCount in the Name list. In the second row, select giBatchSize in the Name list.

12. Click OK to save the modification of the Execute Package task.

13. On the toolbar, click Execute.

    Both packages complete successfully, although one task failed in the UpdateCustomerDim subpackage. All 29 rows were processed in the UpdateCustomerDim subpackage, and 27 of the 29 rows were inserted successfully because the MaximumErrorCount property in the UpdateCustomerDim subpackage was set to 5.

14. Click OK and then click Done.

15. On the toolbar, click Save.

16. Close the MasterUpdate package in DTS Designer, and then close SQL Server Enterprise Manager.

You have successfully configured the MasterUpdate package to dynamically update global variable values in each of the subpackages that it calls based on the values of identically named global variables in the master package. Changes to these global variable values in the MasterUpdate package modify the operation of each subpackage. However, if you want to have different values for the the global variables in each subpackage and have them set dynamically at run time, you can use separate global variables for each subpackage (which can become confusing), or you can use ActiveX Script tasks, Execute SQL tasks, and SQL Server tables to solve this problem. You will learn how to accomplish this in the next chapter.