Creating a Starter DTS Package

Now that you have learned the advantages of creating packages that can be dynamically configured at run time by using initialization files, global variables , and registry entries, you will want to create each new package for the data movement application with these same steps. The easiest way to do this is to create a template package based on one of the existing dimension subpackages. You can then use this prototype as the starting point for each new package that you add to the data movement application.

Note  

If you skipped Chapter 8, execute the IfYouSkippedChapter8.cmd batch file in the C:\Microsoft Press\SQL DTS SBS\Ch9\SkippedChapterFiles folder before you begin these procedures. This batch file restores the SBS_OLTP and SBS_OLAP databases and copies the DTS packages that would have been created in Chapters 1 through 8 into the appropriate folders. It also records the location of the Config.ini initialization file in the Windows registry. If you do not want this batch file to overwrite any packages that you created in Chapters 1 through 8, you must move them or rename them before you execute this batch file.

Create a DTS starter package based on the UpdateProductDim package

1. Open 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. On the design sheet, delete the NewProductsSource connection object and then delete the following steps:

   • Delete Or Add Staging Data

   • Delete Staging Data

   • Log Delete Branch

   • Log Load ProductStage Table Success

   • Log Load ProductStage Table Failure

   • Log Load ProductStage Table Load Rows

   • Bypass Load Dimension Table

   • Log Load Dimension Table Bypass

   • Insert or Update ProductDim Table

   • Log Insert or Update ProductDim Table Success

   • Log Insert or Update ProductDim Table Failure

   • Log Insert or Update ProductDim Table Load Rows

   The remaining steps and connection objects are the initial setup steps that you will use in each new package that you create and the connection objects you will need for these steps, as well as any new steps you add.

   Tip  

   The easiest way to delete a number of steps in a package is to draw a box around them on the design sheet to select them as a group .

4. Right-click an open area on the design sheet and click Disconnected Edit.

5. In the left pane, expand Connections and then click ProductStage.

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

7. Click Close, double-click the ProductStage connection object on the design sheet, and then click OK

   The name of this connection object is now updated to display its new generic name.

8. On the design sheet, double-click the Properties From INI File step, and then delete the following package properties from the Change list:

   • DataSource property for the NewProductData connection object

   • ExceptionFileName property for the Load ProductStage Table step

   • ExceptionFileName property for the Insert Or Update ProductDim Table step

   • LogFileName for the UpdateProductDim package

   These properties were unique to the UpdateProductDim package and are not needed in the template package.

9. Click the first line in the Change List and then click Edit.

   An error appears because you have renamed the ProductStage connection object.

10. Click OK, click SBS_OLAPConnection in the left pane, double-click UDLPath in the right pane, click OK, and then click OK to save the update of this Dynamic Properties task.

11. Click OK to save the modified Dynamic Properties task.

12. On the design sheet, double-click the Bypass Package step.

    The ActiveX Script task called by this step currently references the DeleteOrAddStagingData step, which will not be part of the StarterDTS package. You will modify the script in the Bypass Package step to reference the PropertiesFromGVs step rather than the DeleteOrAddStagingData step.

    

13. In this ActiveX script, locate each reference to the DeleteOrAddStagingData step, and replace it with a reference to the PropertiesFromGVs step. Then click OK to save the modified Bypass Package step.

14. On the design sheet, double-click the Log Package Bypass step.

15. In the SQL Statement box, replace 'UpdateProductDim' with ? (make sure you remove the apostrophes ), replace 'UpdateProductDim Package Was Bypassed' with 'Package Was Bypassed' , and then click Parameters.

16. Click Create Global Variables, type gsPackageName in the Name column, select String in the Type list, type StarterDTS in the Value box, and then click OK.

17. In the Parameter Mapping box, select gsPackageName on the second line, mapped to Parameter 2 in the Parameters column, and then click OK.

    Changing the parameter in the INSERT statement to a global variable and using a more generic phrase for the additional information column allows you to dynamically configure this log entry for each package you create based on this StarterDTS package. You will populate this global variable with the name of the package when you create a new package rather than editing the INSERT statement in each logging step each time you create a new package. This technique can really save you time if you add logging steps to each of the initial tasks in this package.

    

18. Click OK to save the modified Execute SQL task.

19. On the design sheet, double-click the PickupGVs step, replace ˜UpdateProductDim in the WHERE clause of the SELECT statement with ? , and then click Parameters.

20. In the Parameter Mapping box, select gsPackageName on the first line, mapped to Parameter 1, select giConfigID on the second line, mapped to Parameter 2, and then click OK.

    Because the additional parameter was added to the WHERE clause before the parameter that was already mapped, you must change the mapping of the existing parameter as well as add the mapping for the additional parameter.

21. Click OK to save the modified Execute SQL task.

22. On the design sheet, click the Bypass Package step, and then hold down the Ctrl key and click the Properties From GVs step.

23. On the Workflow menu, click On Success.

24. Right-click an open area of the design sheet and click Package Properties.

25. On the logging tab, delete the entry in the Error File box and then click OK.

    By default, each new package based on this StarterDTS package will log its execution to SQL Server. If you want each new package to also log its execution to a text file, you can add the LogFileName property to the Properties From INI File step and then add the appropriate entry to the Config.ini initialization file.

26. On the design sheet, double-click the Properties From GVs step, click Delete to delete the MaximumErrorCount property, click Delete again to delete the InsertCommitSize property, and then click OK.

    These entries in the Change List referred to a task that has been deleted. In the StarterDTS package, you will not dynamically set any task properties from global variables until you create the tasks. However, by leaving the Properties From GVs step as the final step in the StarterDTS package, you can add dynamically configured properties for the tasks that you add to a package created based on the StarterDTS package. However, you will leave the giMaxErrorCount and giInsertCommitSize global variables in the StarterDTS package for use by tasks that you add to packages based on this package.

27. On the Package menu, click Save As.

28. In the Package Name box, type StarterDTS , verify that Structured Storage File is selected in the Location list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\StarterDTS.dts in the File Name box, and then click OK.

    

Now that you have successfully created the StarterDTS package, you are ready to create a subpackage based on it.