Reading Registry Values for Path Locations


Reading Registry Values for Path Locations

The path to the initialization file is hard coded in each Dynamic Properties task in each package in the data movement application. If you want to move the data movement application files to any folder structure other than C:\Microsoft Press\SQL DTS SBS\DataMovementApplication, the packages in the data movement application will not be able to locate the initialization file and they will fail. A Windows application generally stores its installation path in the Windows registry and retrieves that information during execution from a known location in the registry.

Adding a Registry Entry

In the following procedure, you will add a subkey to the Windows registry that contains the location of the Config.ini initialization file. In subsequent procedures, you will configure the MasterUpdate , UpdateProductDim , and UpdateCustomerDim packages to read this subkey value before executing any other steps. This will enable you to move the location of the Config.ini initialization file to another folder, or rename that Config.ini file if necessary.

Add a subkey to the Windows registry that contains the location of the Config.ini initialization file

  1. Using Windows Explorer, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles.

  2. Right-click DMA.reg, and then click Edit to view this text file.

    This text file creates the ConfigFile subkey containing the full path to the initialization file, as well as the BuildNumber and BuildDate subkeys.

    click to expand
  3. Close Notepad, double-click DMA.reg, click Yes to confirm that you want to add the information in this reg file to the Windows registry, and then click OK.

Now that you have recorded the location for the Config.ini file in the Windows registry, you are ready to add a global variable to the MasterUpdate package to store this value and then create an ActiveX Script task that reads the ConfigFile subkey value into this global variable.

Updating the Dynamic Properties Task in the MasterUpdate Package with a Registry Key Value

In the following procedures, you will create an ActiveX Script task in the MasterUpdate package that reads the path to the Config.ini initialization file from the subkey you added to in the Windows registry into a global variable. You will then create another ActiveX Script task in this package that updates the Config.ini initialization file location information stored in DTSTask_DTSDynamicPropertiesTask_1 task (the task called by the Properties From INI File step). You will then ensure that these new steps execute before any other steps in this package.

Read the ConfigFile registry key value into a global variable in the MasterUpdate package by creating an ActiveX Script task

  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 gsConfigINIPath , select String in the Type column, and then click OK.

  5. On the Task menu, click ActiveX Script Task, type Set GV From Registry in the Description box, click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click ReadINIFromRegistry.bas.

    This script begins by declaring a variable to hold the WScript object and then sets this variable to an instance of the WScript object. This script then declares an additional variable and retrieves the value of the ConfigFile subkey from the Windows registry into this variable. This script then sets the value of the gsConfigINIPath global variable in the package to the value retrieved from the Windows registry.

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

Now that you have updated the value of the gsConfigINIPath global variable in the MasterUpdate package from the ConfigFile subkey, you will create an ActiveX Script task that updates the path the Properties From INI File step uses to locate the Config.ini file.

Set the INI file location from the global variable value in the MasterUpdate package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, type Set INI Location From GV , click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click SetINIFromGV.bas.

    This script begins by declaring a variable and then populating the variable with the value of the gsConfigINIPath global variable stored in the package. This script then declares an additional variable and places a reference to the package into this variable. Next the script declares two additional variables, one to hold the task assignments collection from the package and one for each assignment in the package. This script uses a For Each loop to locate each reference to the Config.ini file in the DTSTask_DTSDynamicPropertiesTask_1 task (the task called by the Properties From INI File step) and sets its value to the value of the gsConfigINIPath global variable retrieved from the package.

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

You have configured a step in the MasterUpdate package that reads the Config.ini path from the registry into a global variable and a step that updates the Properties From INI File task with this value. Now you must configure the precedence constraints so that these two steps execute before any other steps in this package.

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

  1. On the design sheet, click the Set GV 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.

    click to expand
  5. On the toolbar, click Save and then close the MasterUpdate package in DTS Designer.

You have now successfully modified each component of the MasterUpdate package that relied on a hard-coded path within the package. Next you must configure the subpackages called by the Execute Package tasks in the MasterUpdate package to read the location of the Config.ini file from the registry as well. If you do not, when you move the files in the data movement application, these packages will fail when they attempt to read the Config.ini file from the hard- coded location rather than the updated location in the initialization file specified in the registry.

Updating the Dynamic Properties Task in the UpdateCustomerDim Package with a Registry Key Value

In the following procedures, you will create an ActiveX Script task in the UpdateCustomerDim package that reads the path to the Config.ini initialization file from the subkey you added to in the Windows registry into a global variable. You will then create another ActiveX Script task in this package that updates the Config.ini initialization file location information stored in DTSTask_DTSDynamicPropertiesTask_1 task (the task called by the Properties From INI File step). You will then ensure that these new steps execute before any other steps in this package.

Read the ConfigFile registry key value into a global variable in the UpdateCustomerDim package by creating an ActiveX Script task

  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 gsConfigINIPath , select String in the Type column, and then click OK.

  5. On the Task menu, click ActiveX Script Task, type Set GV From Registry in the Description box, click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click ReadINIFromRegistry.bas.

    This code is identical to the code used in the MasterUpdate package because it performs exactly the same function in this package.

  6. Click OK to save this ActiveX Script task.

Now that you have updated the value of the gsConfigINIPath global variable in the UpdateCustomerDim package from the ConfigFile subkey, you will create an ActiveX Script task that updates the path that the Properties From INI File step uses to locate the Config.ini file.

Set the INI File location from the global variable value in the UpdateCustomerDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, type Set INI Location From GV , click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click SetINIFromGV.bas.

    This code is identical to the code used in the MasterUpdate package because it performs exactly the same function in this package.

  2. Click OK to save this ActiveX Script task.

You have configured a step in UpdateCustomerDim package that reads the Config.ini path from the registry into a global variable and a step that updates the Properties From INI File task with this value. Now you must configure the precedence constraints so that these two steps execute before any other steps in this 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 GV 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.

    click to expand
  5. On the toolbar, click Save, and then close the UpdateCustomerDim package in DTS Designer.

Now that you have configured the MasterUpdate and UpdateCustomerDim packages to dynamically update the Properties From INI File step with the location of the Config.ini file from the registry, you need to update the Properties From INI File step in the UpdateProductDim package in the same fashion.

Updating the Dynamic Properties Task in the UpdateProductDim Package with a Registry Key Value

In the following procedures, you will create an ActiveX Script task in the UpdateProductDim package that reads the path to the Config.ini initialization file from the subkey you added to in the Windows registry into a global variable. You will then create another ActiveX Script task in this package that updates the Config.ini initialization file location information stored in DTSTask_DTSDynamicPropertiesTask_1 task (the task called by the Properties From INI File step). You will then ensure that these new steps execute before any other steps in this package.

Read the ConfigFile registry key value into a global variable in the UpdateProductDim package by creating an ActiveX Script task

  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 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 gsConfigINIPath , select String in the Type column, and then click OK.

  5. On the Task menu, click ActiveX Script Task, type Set GV From Registry in the Description box, click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click ReadINIFromRegistry.bas.

    This code is identical to the code used in the MasterUpdate and UpdateCustomerDim packages because it performs exactly the same function in this package.

  6. Click OK to save this ActiveX Script task.

Now that you have updated the value of the gsConfigINIPath global variable in the UpdateProductDim package from the ConfigFile subkey, you will create an ActiveX Script task that updates the path the Properties From INI File step uses to locate the Config.ini file.

Set the INI file location from the global variable value in the UpdateProductDim package by creating an ActiveX Script task

  1. On the Task menu, click ActiveX Script Task, type Set INI Location From GV , click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch7\ChapterFiles, and then double-click SetINIFromGV.bas.

    This code is identical to the code used in the MasterUpdate and UpdateCustomerDim packages because it performs exactly the same function in this package.

  2. Click OK to save this ActiveX Script task.

You have configured a step in the UpdateProductDim package that reads the Config.ini path from the registry into a global variable and a step that updates the Properties From INI File task with this value. Now you must configure the precedence constraints so that these two steps execute before any other steps in this 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 GV 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.

    click to expand
  5. On the toolbar, click Save, and then close the UpdateProductDim package in DTS Designer.

Now that you have configured the MasterUpdate , UpdateCustomerDim , and UpdateProductDim packages to dynamically update package and task properties from the registry, an initialization file, and a SQL table, you are ready to test the execution of your packages from a different folder location.

Executing the Data Movement Application Packages from a New Folder Location

In the following procedure, you will copy all of the files required by the data movement application packages to a new folder structure to ensure that you have no hard-coded paths in any of the packages in the data movement application. To ensure the test is valid, you will rename the current data movement application folder in the file system and then execute the packages from the new folder structure.

Copy the data movement application to a new folder and then execute it

  1. Using Windows Explorer, create the DMA2 folder in the C:\Microsoft Press\SQL DTS SBS folder.

  2. Using Windows Explorer, copy all the files in the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder to the C:\Microsoft Press\SQL DTS SBS\DMA2 folder.

  3. On the Windows Start menu, click Run, type Regedt32 in the Open box, and then click OK.

  4. In the Windows registry, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL DTS SBS in the left pane and then double-click ConfigFile in the right pane.

  5. In the Value Data box, change the value to C:\Microsoft Press\SQL DTS SBS\DMA2\Config.ini and then click OK. Do not close the Registry Editor.

  6. Using Windows Explorer, navigate to C:\Microsoft Press\SQL DTS SBS\DMA2 and then double-click Config.ini.

    Notice that all references in the Config.ini initialization file refer to the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder. To test your ability to move the data movement application, you need to change these references to the C:\Microsoft Press\SQL DTS SBS\DMA2 folder.

  7. On the Edit menu, click Replace.

  8. Type DataMovementApplication in the Find What box, type DMA2 in the Replace With box, and then click Replace All.

  9. Click Cancel, click Save on the File menu, and then close Notepad.

    Now that you have changed these references in the initialization file, you will change the name of the original DataMovementApplication folder to ensure that the data movement application files in the DMA2 folder cannot access any of the files that were previously used.

  10. Using Windows Explorer, rename the DataMovementApplication folder to DataMovementApplication2 .

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

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

  13. On the toolbar, click Execute.

    One task in one of the subpackages fails.

  14. Click OK.

    Notice that the error occurred in the UpdateCustomerDim package.

  15. Double-click Call UpdateCustomerDim Subpackage in the Status window to verify that a task in this subpackage failed because the number of failing rows exceeded the maximum specified. (If it fails for any other reason, you have made an error on one of the configuration steps.)

  16. Click OK and then click Done.

  17. Close the MasterUpdate package.

  18. Switch to the Registry Editor and then change the value of the ConfigFile key to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini and then close the Registry Editor.

  19. Using Windows Explorer, rename the DataMovementApplication2 folder to DataMovementApplication .

Your data movement application is now portable. Next you will learn how to create multiple branches in your application s packages using ActiveX Script tasks.




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

Similar book on Amazon

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