Enabling Package Branching


You can add different tasks for different situations in the same package and then use an ActiveX Script task to evaluate one or more global variable values at execution time to determine which of the tasks in a package should be called for a particular execution of the package. For example, sometimes you might want to have a package that normally adds new data to a staging table, delete data that has already been copied into the dimension tables before new data is added to the staging table. In a data warehouse environment, updates to the data in the dimension and fact tables are generally processed on a regular schedule (such as weekly) into the dimension and fact tables for the multidimensional cube. However, for a variety of reasons, you may want to add data to one or more staging tables incrementally during each week. In such a case, you will want to incrementally add data to the staging table until the weekly processing occurs. After you have updated the fact and dimension tables and processed their dimensions and partitions as appropriate (and ensured the data is processed successfully), you need to delete the data in the staging tables before you add any new data for the next week (to avoid adding duplicate data). Although you could create a new package associated with each staging table just for this delete task, you can also simply add a branch to each existing update package to perform the delete task. Reusing existing packages enables you to reuse the existing dynamic logic, use the same package names and GUIDs, and use the same DTSRun batch files. You can simply specify at run time whether the update branch or the delete branch of each package is executed by using a configuration ID. The package will use the configuration ID to retrieve appropriate global variable values from the global variable configuration SQL table to call the appropriate tasks for the delete branch of the package.

In addition, you might want to include an option in each package that enables you to bypass a package for a particular execution of the data movement application. For example, suppose you wanted to add data to only one particular dimension table. In the data movement application, a subpackage is created for each dimension table, and each time the data movement application is executed, the master package executes all of these subpackages. By adding a bypass branch to each subpackage, you can call the bypass branches of the appropriate subpackages. This enables you to control which subpackages actually add data and which ones simply log the fact that they were bypassed for a particular execution of the data movement application.

Adding a Branching Variable to the MasterUpdate Package

In the following procedure, you will create a global variable in the MasterUpdate package with a default value of 0 and pass this global variable to each subpackage. An ActiveX Script task in each subpackage will use this global variable to determine whether to execute the default workflow or a delete branch that you will add to each of these subpackages.

Add the DeleteOrAdd global variable to the MasterUpdate package and pass it to subpackages by editing the Execute Package tasks

  1. Switch to SQL Server Enterprise Manager, and then right-click Data Transformation Services in your local instance.

  2. Click Open Package and then open the most recent version of the MasterUpdate package in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder using a password of mypassword .

  3. Click Properties on the Package menu, and then click the Global Variables tab.

  4. In the first empty line in the Name column, type gbDeleteOrAdd , select Boolean in the Type list, type in the Value list, and then click OK.

    The value for this global variable will be passed to each subpackage and used to determine whether to execute the update or delete branch in each subpackage. You can change the value of this global variable at run time by placing a different value for it in the PackageGVs table and associating that value with the configuration ID that you pass when you execute the MasterUpdate package. The value for this variable will govern the execution flow in each subpackage unless a different value is specified in the PackageGVs table for a particular subpackage.

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

  6. In the first empty line in the Names list, click gbDeleteOrAdd in the Variables list and then click OK.

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

  8. In the first empty line in the Names list, click gbDeleteOrAdd in the Variables list and then click OK.

  9. On the toolbar, click Save and then close the MasterUpdate package in DTS Designer.

Now that you have added this bypass package global variable to the MasterUpdate package and passed its value to each subpackage, you are ready to use this variable in each subpackage. You did not add a bypass package global variable to the MasterUpdate package because that package will always be executed.

Adding Branching Steps to the UpdateCustomerDim Package

In the following procedures, you will create the gbDeleteOrAdd , the gbBypassPackage , and the gbBypassLoadDimensionTable global variables in the UpdateCustomerDim package. You will then create a delete or add staging data step and a bypass package step in this package. (When you add a dimension table loading step to the UpdateProductDim package in Chapter 8, you will also add a bypass load dimension table step to that subpackage.) You will create ActiveX Script tasks in this package to read these variable values and determine the appropriate steps in the workflow based on the values of the these global variables. By default, the package bypass step will simply call the next step in the package unless the value of the bypass global variable is changed from 0 to 1. If it is changed to 1, the package bypass step will call a logging step that will log the fact that the remaining steps in the package were bypassed. By default, the delete or add staging data step will simply call the load staging table step unless the value of the delete or add global variable is changed from 0 to 1. If it is changed to 1, the delete or add staging data step will call a delete staging data step, which in turn will call a logging step that will log the fact that the delete staging data step executed.

Add branching global variables to the UpdateCustomerDim package

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

  2. Click Open Package and then open the most recent version of the UpdateCustomerDim package in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder using a password of mypassword .

  3. Click Properties on the Package menu, and then click the Global Variables tab.

  4. In the first empty line in the Name column, type gbDeleteOrAdd , select Boolean in the Type list, and then type in the Value list.

    This global variable with a default value of 0 will indicate that new data will be added to the CustomerStage table from the data source. A value of 1 will indicate that all existing data in this table will be deleted. This value is passed from the MasterUpdate package, but it might be overwritten by a value retrieved from the PackageGVs table for this package.

  5. In the next empty line in the Name column, type gbBypassPackage , select Boolean in the Type list, and then type in the Value list.

    This global variable with a default value of 0 will indicate that the tasks in this package after the branching step will be executed for a particular execution of the data movement application. A value of 1 will indicate that the tasks in this package after the branching step should not be executed for a particular execution of the data movement application. A value for this global variable in the PackageGVs table for this package will override the default value.

  6. In the next empty line in the Name column, type gbBypassLoadDimensionTable , select Boolean in the Type list, type in the Value list, and then click OK.

    You will use this global variable in Chapter 8 when you add a dimension table load step to this package.

Now that you have created these global variables in the UpdateCustomerDim package, you will create branching steps that will use these global variables.

Add a delete or add staging data branching step to the UpdateCustomerDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, and then type Delete Or Add Staging Data in the Description box.

  2. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click DeleteOrAddStagingData.bas.

    This script begins by declaring a variable and then placing a reference to the package into the variable. This script then enables the DeleteStagingData and the PropertiesFromGVs steps in the UpdateCustomerDim package using the DisableStep property of the Step object. (This ensures a known state for these objects.) Next the script reads the value of the gbDeleteOrAdd global variable from the DTSGlobalVariables object and then enables the appropriate step based on this variable s value. Notice that the CInt conversion function is used to convert the value to an integer so that it can be compared to the value of 0. Notice also that this script uses friendly names to refer to steps in the package rather than the DTS-generated names. In the procedures that follow, you will rename each step in the package to a user -friendly name.

    Tip  

    When writing the code for an ActiveX Script task that will perform branching, creating user-friendly names for each step in the package will make your coding and the reuse of existing code between packages easier. As you have seen, the default names for tasks and steps are not user-friendly. Renaming steps to user-friendly names will also make it easier to read the log files.

    click to expand
  3. Click OK to save this ActiveX Script task.

Now that you have created the Delete Or Add Staging Data branching step, you will add a delete staging data step to this package. This step will call an Execute SQL task to delete all data in the CustomerStage table when the value of the gbDeleteOrAdd global variable is set to 1.

Add a delete staging data step to the UpdateCustomerDim package by creating an Execute SQL task

  1. On the Task menu, click Execute SQL Task, and then type Delete Staging Data in the Description box.

  2. In the Existing Connection list, select SBS_OLAPAdditionalConnection.

  3. Type TRUNCATE TABLE CustomerStage in the SQL Statement box, and then click OK to save this Execute SQL task.

Now that you have created the Delete Staging Data step, you will add a bypass package step to this package.

Add a bypass package step to the UpdateCustomerDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, and then type Bypass Package in the Description box.

  2. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click BypassPackage.bas.

    This script is very similar to the DeleteOrAddStagingData script you used earlier. You will notice, however, that this script calls a LogBypassPackage step. This step will log the fact that the package was bypassed each time the data movement application executes and bypasses this package. You will fully configure this task and learn about creating a customized logging solution in Chapter 8. For now, you will simply configure a SQL statement that generates a null result set.

    click to expand
  3. Click OK to save this ActiveX Script task.

Now that you have created the Bypass Package step, you will add a log package bypass step to this package.

Add a log package bypass step to the UpdateCustomerDim package by creating an Execute SQL task

  1. On the Task menu, click Execute SQL Task, and then type Log Package Bypass in the Description box.

  2. Select SBS_OLAPAdditionalConnection in the Existing Connection list, and then type the following Transact-SQL statement in the SQL Statement box:

     UPDATE CustomerStageSET CustomerName = CustomerNameWHERE 0 = 1 

    This Execute SQL task is simply holding a spot for a more complex logging and error handling statement, which you will implement in Chapter 8.

    click to expand
  3. Click OK to save this Execute SQL task.

You have successfully created three branching global variables, two branching steps, branching tasks, and logging steps in the UpdateCustomerDim package.

Configuring Steps and Execution Order in the UpdateCustomerDim Package

In the following procedures, you will configure user-friendly names for each of the steps in the UpdateCustomerDim package to make it easier to understand errors that appear in the log files and then ensure that each step executes in the proper order. Before you change the name of a step that has an existing precedence constraint, you must delete all existing constraints to avoid corrupting a package.

Delete existing precedence constraints and then create user-friendly names for each step in the UpdateCustomerDim package

  1. On the design sheet, delete all six On Success constraints in the package and then arrange the steps on the design sheet according to their execution flow.

    click to expand
    Important  

    You must delete all constraints that reference a step in a package before renaming the step, or the package could become corrupt.

  2. On the Package menu, click Disconnected Edit.

  3. In the left pane, expand Steps (make sure you expand Steps and not Tasks) and then click DTSStep_DTSDataPumpTask_1.

  4. In the right pane, double-click Name in the right pane, type LoadCustomerStageTable in the Value box (replacing the displayed name DTSStep_DTSDataPumpTask_1), and then click OK.

  5. In the left pane, click DTSStep_DTSDynamicPropertiesTask_1.

  6. In the right pane, double-click Name, type PropertiesFromINIFile in the Value box in place of DTSStep_DTSDynamicPropertiesTask_1, and then click OK.

  7. In the left pane, click DTSStep_DTSExecuteSQLTask_1.

  8. In the right pane, double-click Name, type PickupGVs in the Value box in place of DTSStep_DTSExecuteSQLTask_1, and then click OK.

  9. In the left pane, click DTSStep_DTSActiveScriptTask_1.

  10. In the right pane, double-click Name, type SetGVs in the Value box in place of DTSStep_DTSActiveScriptTask_1, and then click OK.

  11. In the left pane, click DTSStep_DTSDynamicPropertiesTask_2.

  12. In the right pane, double-click Name, type PropertiesFromGVs in the Value box in place of DTSStep_DTSDynamicPropertiesTask_2, and then click OK.

  13. In the left pane, click DTSStep_DTSActiveScriptTask_2.

  14. In the right pane, double-click Name, type SetGVFromRegistry in the Value box in place of DTSStep_DTSActiveScriptTask_2, and then click OK.

  15. In the left pane, click DTSStep_DTSActiveScriptTask_3.

  16. In the right pane, double-click Name, type SetINILocationFromGV in the Value box in place of DTSStep_DTSActiveScriptTask_3, and then click OK.

  17. In the left pane, click DTSStep_DTSActiveScriptTask_4.

  18. In the right pane, double-click Name, type DeleteOrAddStagingData in the Value box in place of DTSStep_DTSActiveScriptTask_4, and then click OK.

  19. In the left pane, click DTSStep_DTSExecuteSQLTask_2.

  20. In the right pane, double-click Name, type DeleteStagingData in the Value box in place of DTSStep_DTSExecuteSQLTask_2, and then click OK.

  21. In the left pane, click DTSStep_DTSActiveScriptTask_5.

  22. In the right pane, double-click Name, type BypassPackage in the Value box in place of DTSStep_DTSActiveScriptTask_5, and then click OK.

  23. In the left pane, click DTSStep_DTSExecuteSQLTask_3.

  24. In the right pane, double-click Name, type LogPackageBypass in the Value box in place of DTSStep_DTSExecuteSQLTask_3, and then click OK.

  25. Click Close.

    click to expand

Now that you have configured user-friendly names for the steps in this package, you are ready to configure the appropriate precedence constraints in the UpdateCustomerDim package.

Ensure package steps execute in the proper order by configuring precedence constraints in the UpdateCustomerDim package

  1. On the design sheet, click the Set GVs From Registry step, and then hold down the Ctrl key and click the Set INI Location From GV step.

  2. On the Workflow menu, click On Success.

  3. On the design sheet, click the Set INI Location From GV step, and then hold down the Ctrl key and click the Properties From INI File step.

  4. On the Workflow menu, click On Success.

  5. On the design sheet, click the Properties From INI File step, and then hold down the Ctrl key and click the PickupGVs step.

  6. On the Workflow menu, click On Success.

  7. On the design sheet, click the PickupGVs step, and then hold down the Ctrl key and click the SetGVs step.

  8. On the Workflow menu, click On Success.

  9. On the design sheet, click the SetGVs step, and then hold down the Ctrl key and click the Bypass Package step.

    The Bypass step is the first branching step in the UpdateCustomerDim package. This step determines, based on the value of the gbBypassPackage global variable, whether the package workflow continues and additional tasks execute or whether only the Log Package Bypass step is executed.

  10. On the Workflow menu, click On Success.

  11. On the design sheet, click the Bypass Package step, and then hold down the Ctrl key and click the Log Package Bypass step.

  12. On the Workflow menu, click On Success.

    The Log Package Bypass step will execute only if the Bypass Package step does not disable the Log Package Bypass step based on the value of the gbBypassPackage global variable. With a default value of 0, the Log Package Bypass step is disabled by the Bypass Package step.

  13. On the design sheet, click the Bypass Package step, and then hold down the Ctrl key and click the Delete Or Add Staging Data step.

    If the Bypass Package step directs the package workflow to the Delete Or Add Staging Data step, this step determines whether the Delete Staging Data step executes or the remaining steps in the package execute, based on the value of the gbDeleteOrAdd global variable value.

  14. On the Workflow menu, click On Success.

    Notice that there are two success paths out of the Bypass Package step. Only one of these two paths out of the ActiveX Script task will actually execute, although by looking at the design sheet it appears as if both steps will execute.

    Tip  

    To clearly indicate to someone looking at a package for the first time that a step is a branching step, use a descriptive name for the step. The design sheet makes it appear that both tasks constrained by the branch step will execute when the branching step completes. The logic of the ActiveX Script task called by the branching step actually determines which of the two tasks will execute.

  15. On the design sheet, click the Delete Or Add Staging Data step, and then hold down the Ctrl key and click the Delete Staging Data step.

  16. On the Workflow menu, click On Success.

    The Delete Staging Data step will execute only if the Delete or Add Staging Data step does not disable the Delete Staging Data step based on the value of the gbDeleteOrAdd global variable. With a default value of 0, the Delete Staging Data step is disabled by the Delete Or Add Staging Data step.

  17. On the design sheet, click the Delete Or Add Staging Data step, and then hold down the Ctrl key and click the Properties From GVs step.

  18. On the Workflow menu, click On Success.

    The Properties From GVs step will execute if the value of the gbBypassPackage global variable is 0 and the value of the gbDeleteOrAdd global variable is also 0.

  19. On the design sheet, click the Properties From GVs step, and then hold down the Ctrl key and click the NewCustomersSource connection object.

  20. On the Workflow menu, click On Success.

  21. On the toolbar, click Save and then close the UpdateCustomerDim package.

    click to expand

You have successfully configured two branching steps in the UpdateCustomerDim package and configured user-friendly names for each step in the package. Next you will add branching steps to the UpdateProductDim package.

Adding Branching Steps to UpdateProductDim Package

In the following procedures, you will create the gbDeleteOrAdd , the gbBypassPackage , and the gbBypassLoadDimensionTable global variables in the UpdateProductDim package. You will then create a delete or add staging data step, a bypass package step, and a bypass load dimension table step in this package. You will create ActiveX Script tasks in each package to read these variable values and determine the appropriate steps in the workflow of each package based on the values of the these global variables. By default, the package bypass step will simply call the next step in the package unless the value of the bypass global variable is changed from 0 to 1. If it is changed to 1, the package bypass step will call a logging step that will log the fact that the remaining steps in the package were bypassed. By default, the delete or add staging data step will simply call the load staging table step unless the value of the bypass global variable is changed from 0 to 1. If it is changed to 1, the delete or add staging data step will call a delete staging data step, which in turn will call a logging step that will log the fact that the delete staging data step executed. The bypass load dimension table step will simply call the load dimension table step unless the value of the global variable is changed from 0 to 1. If it is changed to 1, the bypass load dimension table step will call a logging step that will log the fact that the load dimension table step was bypassed.

Add global variables to the UpdateProductDim package

  1. Switch to the console root in SQL Server Enterprise Manager, and then right-click Data Transformation Services in your local instance.

  2. Click Open Package and then open the most recent version of the UpdateProductDim package in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder using a password of mypassword .

  3. Click Properties on the Package menu, and then click the Global Variables tab.

  4. In the first empty line in the Name column, type gbDeleteOrAdd , select Boolean in the Type list, and then type in the Value list.

    This global variable with a default value of 0 will indicate that new data will be added to the ProductStage table. A value of 1 will indicate that all existing data in this table will be deleted. This global variable value is passed from the MasterUpdate package, but it might be overwritten by a value retrieved from the PackageGVs table for this package.

  5. In the next empty line in the Name column, type gbBypassPackage , select Boolean in the Type list, and then type in the Value list.

    This global variable with a default value of 0 will indicate that the tasks in this package after the branching step will be executed for a particular execution of the data movement application. A value of 1 will indicate that the tasks in this package after the branching step should not be executed for a particular execution of the data movement application. A value for this global variable in the PackageGVs table for this package will override the default value.

  6. Type gbBypassLoadDimensionTable in the next empty line in the Name column, select Boolean in the Type list, type in the Value list, and then click OK.

    This global variable with a default value of 0 will indicate that the data in the staging table should be inserted into or update existing data in the ProductDim table. A value of 1 will indicate that after data has been added to the ProductStage table, the package will terminate without inserting or updating data in the ProductDim table.

Now that you have created these global variables in the UpdateProductDim package, you will add branching steps that will use these global variables.

Add a delete or add staging data branching step to the UpdateProductDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, and then type Delete Or Add Staging Data in the Description box.

  2. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click DeleteOrAddStagingData.bas.

    This script is identical to the script used in the UpdateCustomerDim package because it performs the same task in this package. By configuring user-friendly names for all steps, you can ensure that the step names are accurate. If you use the step names generated by DTS (which are partially based on the order in which steps were created), you might have to modify each script to match the names generated by DTS.

  3. Click OK to save this ActiveX Script task.

Now that you have created the Delete Or Add Staging Data branching step, you will add a delete staging data step to this package. This step will call an Execute SQL task to delete all data in the ProductStage table when the value of the gbDeleteOrAdd global variable is set to 1.

Add a delete staging data step to the UpdateProductDim package by creating an Execute SQL task

  1. On the Task menu, click Execute SQL Task, and then type Delete Staging Data in the Description box.

  2. In the Existing Connection list, select SBS_OLAPAdditionalConnection.

  3. In the SQL Statement box, type TRUNCATE TABLE ProductStage , and then click OK to save the Execute SQL task.

Now that you have created the Delete Staging Data step, you will add a bypass package step to this package.

Add a bypass package step to the UpdateProductDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, and then type Bypass Package in the Description box.

  2. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click BypassPackage.bas.

    This script is identical to the script used in the UpdateCustomerDim package because it performs the same task in this package.

  3. Click OK to save this ActiveX Script task.

Now that you have created the Bypass Package step, you will add a log package bypass step to this package.

Add a log package bypass step to the UpdateProductDim package by creating an Execute SQL task

  1. On the Task menu, click Execute SQL Task, and then type Log Package Bypass in the Description box.

  2. Select SBS_OLAPAdditionalConnection in the Existing Connection list, and then type the following Transact-SQL statement in the SQL Statement box:

     UPDATE ProductStageSET ProductName = ProductNameWHERE 0 = 1 

    This Execute SQL task is simply holding a spot for a more complex logging and error handling statement, which you will implement in Chapter 8.

  3. Click OK to save this Execute SQL task.

Now that you have created the Log Package Bypass step, you will add a bypass load dimension table step to this package.

Add a bypass load dimension table step to the UpdateProductDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, and then type Bypass Load Dimension Table in the Description box.

  2. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click BypassLoadDimensionTable.bas.

    This script is very similar to the previous scripts you used to create branching tasks.

  3. Click OK to save this ActiveX Script task.

Now that you have created the Bypass Load Dimension Table step, you will add a log load dimension table bypass step to this package.

Add a log load dimension table bypass step to the UpdateProductDim package by creating an Execute SQL task

  1. On the Task menu, click Execute SQL Task, and then type Log Load Dimension Table Bypass in the Description box.

  2. Select SBS_OLAPAdditionalConnection in the Existing Connection list, and then type the following Transact-SQL statement in the SQL Statement box:

     UPDATE ProductStageSET ProductName = ProductNameWHERE 0 = 1 

    This Execute SQL task is simply holding a spot for a more complex logging and error handling statement, which you will implement in Chapter 8.

  3. Click OK to save this Execute SQL task.

You have successfully created three branching global variables, three branching steps, branching tasks, and logging steps in the UpdateProductDim package.

Configuring Steps and Execution Order in the UpdateProductDim Package

In the following procedures, you will configure user-friendly names for each of the steps in the UpdateProductDim package to make it easier to understand errors that appear in the log files and then ensure that each step executes in the proper order. Before you change the name of a step that has an existing precedence constraint, you must delete all existing constraints to avoid corrupting a package.

Delete existing precedence constraints and then create user- friendly names for each step in the UpdateProductDim package

  1. On the design sheet, delete all six On Success constraints and the one On Completion constraint in the package and arrange the steps on the design sheet according to their execution flow.

    click to expand
  2. On the Package menu, click Disconnected Edit.

  3. In the left pane, expand Steps (again, make sure you expand Steps and not Tasks) and then click DTSStep_DTSDataPumpTask_1.

  4. In the right pane, double-click Name, type LoadProductStageTable in the Value box (replacing the displayed name DTSStep_DTSDataPumpTask_1), and then click OK.

  5. In the left pane, click DTSStep_DTSDataDrivenQueryTask_1.

  6. In the right pane, double-click Name, type InsertOrUpdateProductDimTable in the Value box in place of DTSStep_DTSDataDrivenQueryTask_1, and then click OK.

  7. In the left pane, click DTSStep_DTSDynamicPropertiesTask_1.

  8. In the right pane, double-click Name, type PropertiesFromINIFile in the Value box in place of DTSStep_DTSDynamicPropertiesTask_1, and then click OK.

  9. In the left pane, click DTSStep_DTSExecuteSQLTask_1.

  10. In the right pane, double-click Name, type PickupGVs in the Value box in place of DTSStep_DTSExecuteSQLTask_1, and then click OK.

  11. In the left pane, click DTSStep_DTSActiveScriptTask_1.

  12. In the right pane, double-click Name, type SetGVs in the Value box in place of DTSStep_DTSActiveScriptTask_1, and then click OK.

  13. In the left pane, click DTSStep_DTSDynamicPropertiesTask_2.

  14. In the right pane, double-click Name, type PropertiesFromGVs in the Value box in place of DTSStep_DTSDynamicPropertiesTask_2, and then click OK.

  15. In the left pane, click DTSStep_DTSActiveScriptTask_2.

  16. In the right pane, double-click Name, type SetGVFromRegistry in the Value box in place of DTSStep_DTSActiveScriptTask_2, and then click OK.

  17. In the left pane, click DTSStep_DTSActiveScriptTask_3.

  18. In the right pane, double-click Name, type SetINILocationFromGV in the Value box in place of DTSStep_DTSActiveScriptTask_3, and then click OK.

  19. In the left pane, click DTSStep_DTSActiveScriptTask_4.

  20. In the right pane, double-click Name, type DeleteOrAddStagingData in the Value box in place of DTSStep_DTSActiveScriptTask_4, and then click OK.

  21. In the left pane, click DTSStep_DTSExecuteSQLTask_2.

  22. In the right pane, double-click Name, type DeleteStagingData in the Value box in place of DTSStep_DTSExecuteSQLTask_2, and then click OK.

  23. In the left pane, click DTSStep_DTSActiveScriptTask_5.

  24. In the right pane, double-click Name, type BypassPackage in the Value box in place of DTSStep_DTSActiveScriptTask_5, and then click OK.

  25. In the left pane, click DTSStep_DTSExecuteSQLTask_3.

  26. In the right pane, double-click Name, type LogPackageBypass in the Value box in place of DTSStep_DTSExecuteSQLTask_3, and then click OK.

  27. In the left pane, click DTSStep_DTSActiveScriptTask_6.

  28. In the right pane, double-click Name, type BypassLoadDimensionTable in the Value box in place of DTSStep_DTSActiveScriptTask_6, and then click OK.

  29. In the left pane, click DTSStep_DTSExecuteSQLTask_4.

  30. In the right pane, double-click Name, type LogLoadDimensionTableBypass in the Value box in place of DTSStep_DTSExecuteSQLTask_4, and then click OK.

  31. Click Close.

Now that you have configured user-friendly names for the steps in this package, you are ready to configure the appropriate precedence constraints in the UpdateProductDim package.

Ensure package steps execute in the proper order by configuring precedence constraints in the UpdateProductDim package

  1. On the design sheet, click the Set GVs From Registry step, and then hold down the Ctrl key and click the Set INI Location From GV step.

  2. On the Workflow menu, click On Success.

  3. On the design sheet, click the Set INI Location From GV step, and then hold down the Ctrl key and click the Properties From INI File step.

  4. On the Workflow menu, click On Success.

  5. On the design sheet, click the Properties From INI File step, and then hold down the Ctrl key and click the PickupGVs step.

  6. On the Workflow menu, click On Success.

  7. On the design sheet, click the PickupGVs step, and then hold down the Ctrl key and click the SetGVs step.

  8. On the Workflow menu, click On Success.

  9. On the design sheet, click the SetGVs step, and then hold down the Ctrl key and click the Bypass Package step.

  10. On the Workflow menu, click On Success.

  11. On the design sheet, click the Bypass Package step, and then hold down the Ctrl key and click the Log Package Bypass step.

  12. On the Workflow menu, click On Success.

  13. On the design sheet, click the Bypass Package step, and then hold down the Ctrl key and click the Delete Or Add Staging Data step.

  14. On the Workflow menu, click On Success.

  15. On the design sheet, click the Delete Or Add Staging Data step, and then hold down the Ctrl key and click the Delete Staging Data step.

  16. On the Workflow menu, click On Success.

  17. On the design sheet, click the Delete or Add Staging Data step, and then hold down the Ctrl key and click the Properties From GVs step.

  18. On the Workflow menu, click On Success.

  19. On the design sheet, click the Properties From GVs step, and then hold down the Ctrl key and click the NewProductsSource connection object.

  20. On the Workflow menu, click On Success.

  21. On the design sheet, click the ProductStage connection object, and then hold down the Ctrl key and click the Bypass Load Dimension Table step.

  22. On the Workflow menu, click On Completion.

    Tip  

    By configuring the phase of this package that loads data from the ProductStage table into the ProductDim table to execute regardless of the success or failure of the previous steps in the package, you can execute the Insert Or Update Dimension Table step after the failure of the Load ProductStage Table step. For example, this step might fail because no source file is provided. You might not provide a source file because you have already loaded this source data into the ProductStage table in a previous execution of this package or because no new dimension data needs to be added to the ProductStage table for the current time period.

  23. On the design sheet, click the Bypass Load Dimension Table step, and then hold down the Ctrl key and click the Log Load Dimension Table Bypass step.

  24. On the Workflow menu, click On Success.

  25. On the design sheet, click the Bypass Load Dimension Table step, and then hold down the Ctrl key and click the Insert Or Update ProductDim Table step.

  26. On the Workflow menu, click On Success.

  27. On the toolbar, click Save. Do not close the UpdateProductDim package.

    click to expand

You have successfully configured two branching steps in the UpdateCustomerDim package and the three branching steps in the UpdateProductDim package. You have also configured the MasterUpdate package to pass the value of gbDeleteOrUpdate global variable to these subpackages.

Creating Delete Configurations to Execute the Branches in the Subpackages

In the following procedures, you will add global variable configuration values to the PackageGVs table to create three separate delete configurations in the PackageGVs table and then execute the data movement application packages using these new configurations.

Create delete configurations in the PackageGVs table

  1. Switch to SQL Query Analyzer and then click Load SQL Script on the toolbar.

  2. Navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles in the Look In list and then double-click DeleteConfigs.sql.

    This script adds three configurations to the PackageGVs table. The first configuration sets the value of the gbDeleteOrAdd global variable to 1 in the MasterUpdate package. Since no other value is set in either of the subpackages and since the value of the gbBypassPackage global variable is not changed, the delete phase of each subpackage will execute if the MasterUpdate package is run using a giConfigID value of 3. The second configuration sets the value of the gbDeleteOrAdd global variable to 1 in the UpdateCustomerDim package and sets the value of the gbBypassPackage global variable to 1 in the UpdateProductDim package. When the MasterUpdate package is run using a giConfigID value of 4, the delete phase of the UpdateCustomerDim package is executed and the bypass phase of the UpdateProductDim package is executed. The third configuration is the reverse of the second configuration, enabling you to delete data in the ProductStage table without deleting data in the CustomerStage table.

    click to expand
  3. On the toolbar, click Execute to add these entries to the PackageGVs table and then close SQL Query Analyzer.

You have now created three new global variable configurations in the PackageGVs table and are ready to begin testing the branching steps using these configurations of global variables.

Test package execution using different configuration values

  1. Switch to the UpdateProductDim package in DTS Designer, and then click Execute on the toolbar.

    The package completes successfully.

  2. Click OK and then in the Status window, review the steps that did not execute.

    Notice that three steps did not execute: the Delete Staging Data step, the Log Package Bypass step, and the Log Load Dimension Table Bypass step. Since the UpdateProductDim package executed using a giConfigID value of 1, none of the non-default steps in this package executed.

    click to expand
  3. Click Done.

  4. On the Package menu, click Properties.

  5. On the Global Variables tab, change the value for the gbDeleteOrAdd global variable to 1 and then click OK.

    Tip  

    The only valid values for a Boolean type global variable are 0 or “1. If you set the value to anything other than 0 and save it, you will discover that its value was changed to “1 the next time you open the package properties. 0 indicates False , and anything other than 0 becomes “1, which indicates True .

  6. On the toolbar, click Execute.

    The package completes successfully.

  7. Click OK and then in the Status window, review the steps that executed.

    Notice that the Delete Staging Data step executed after the Delete Or Add Staging Data step because the value of the gbDeleteOrAdd global variable was changed from 0 to 1.

    click to expand
  8. Click Done.

  9. On the Package menu, click Properties.

  10. On the Global Variables tab, change the value of the giConfigID global variable to 5 , and then click OK.

  11. On the toolbar, click Execute.

    The package completes successfully.

  12. Click OK and then in the Status window, review the steps that executed.

    Notice that the Log Package Bypass step executed after the Bypass Package step because a non-default gbBypassPackage global variable value was retrieved from the PackageGVs table by the PickUpGVs step and then updated in the package by the SetGVs step.

    click to expand
  13. Click Done.

  14. On the Package menu, click Properties and then click the Global Variables tab.

    Notice that the value of the gbBypassPackage global variable was changed to -1.

  15. Click OK and then close the UpdateProductDim package in DTS Designer. Do not save any changes.

  16. Close SQL Server Enterprise Manager.

  17. Using Windows Explorer, navigate to the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder, right-click MasterUpdateDefaultConfig.cmd, and click Edit.

  18. Change the value of the giConfigID global variable parameter from 1 to 3 , and then click Save As on the File menu.

  19. Type DeleteAllStagingData.cmd in the File Name box, select All Files in the Save As Type list, click Save, and then close Notepad.

  20. Double-click DeleteAllStagingData.cmd in the DataMovementApplication folder to delete all staging data in the ProductStage and the CustomerStage tables in the SBS_OLAP database.

  21. After the batch completes, open the UpdateProductDimPackageErrorLog.txt file and then scroll to the end of the file to verify that the DeleteStagingData step executed.

    The steps in the log file are easier to interpret because you changed the DTS-generated step names to user-friendly names. Notice also that each step that did not execute is displayed in this log file along with each step that did execute. If you review the log saved to SQL Server, you will see that only the steps that executed successfully are logged to SQL Server.

    click to expand
  22. Close the UpdateProductDimPackageErrorLog.txt file in Notepad and then open the UpdateCustomerDimPackageErrorLog.txt file to verify that the DeleteStagingData step executed and then close Notepad.




Microsoft SQL Server 2000 DTS Step by Step
Microsoft SQL Server 2000 DTS Step by Step (Step by Step (Microsoft))
ISBN: 0735619166
EAN: 2147483647
Year: 2003
Pages: 87
Authors: Carl Rabeler

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net