Using the Dynamic Properties Task


The Dynamic Properties task enables you to modify the properties of virtually any item within a DTS package at run time. The Dynamic Properties task works by retrieving values from sources outside the DTS package at run time and assigning the values retrieved to selected package and task properties. Values can be retrieved from one or more of the following sources:

  • INI file field A single .ini file can contain multiple fields for many different properties within a package, and the fields are grouped into one or more sections. Each field in an .ini file is limited to a single line.

  • Data file contents A data file supports multiple lines for a property value but can contain only the value for a single package property.

  • Query column Values can be retrieved from the first column of the first row returned from a query.

  • Global variable value Global variable values can be changed at run time by using the DTSRun command, an ActiveX Script task, or an Execute Package task.

  • Environmental variable value Values can be retrieved from a System or User variable, such as COMPUTERNAME or PATH .

  • Constant value You can set a package or task property to a constant so that you can return the property to a known value or a default value in between tasks .

Storing values outside the packages in a data movement application enables these values to be modified at run time; you do not have to open and edit multiple packages to modify the values. As already discussed, dynamic configuration is very useful when migrating the data movement application. Dynamic configuration also enables you to execute the packages in the data movement application under circumstances that change, such as when the batch size or the maximum error count settings need to be updated.

In the following procedures, you will create an initialization file that provides the paths to the UDL files you created in the previous procedure, as well as the paths to the delimited text files used in the data movement application. You will then create Dynamic Properties tasks in each package that uses this initialization file. You will also create a global variable that one of these Dynamic Properties tasks will use to dynamically configure the number of permitted errors before a task terminates.

Create an initialization file

  1. Open Microsoft Notepad and then open the Config.txt file in C:\Microsoft Press\SQL DTS SBS\Ch6\ChapterFiles folder.

    This text file contains four sections: UDL, Products, Customers, and HistoricalData. Each section contains path and file name information that the data movement application can use to dynamically configure tasks and connection objects.

    click to expand
  2. On the File menu, click Save As.

  3. In the Save In list, navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication.

  4. Type Config.ini in the File Name box, select All Files in the Save As Type list, select Unicode in the Encoding list, and then click Save. Do not close Notepad.

You have successfully created an initialization file. You can add or modify sections and keys in this initialization file based on the requirements of your data movement application.

Configure a Dynamic Properties task in the UpdateProductDim package

  1. Switch to the UpdateProductDim package in DTS Designer.

  2. On the Task menu, click Dynamic Properties task and then type Properties From INI File in the Description box.

    In this procedure, you will configure this task to dynamically set the properties of the connection objects in this package.

    click to expand
  3. Click Add to select a property to be dynamically set.

    The Dynamic Properties Task: Package Properties dialog box displays the connection, task, step, and global variable objects in the UpdateProductDim package in the left pane, and the properties and values for the selected object in the right pane.

  4. Expand each node in the console tree to see each object in the UpdateProductDim package.

    Notice that each task has a step associated with it and that the names for the tasks and steps are generated by DTS when the task is created. The name you enter when you create a task is its description, not its name.

    click to expand
  5. Select the Leave This Dialog Box Open After Adding A Setting check box.

    Tip  

    The Package Properties dialog box will close after each property is modified if you do not select the Leave This Dialog Box Open After Adding A Setting check box. Leaving the dialog box open after each modification will save you time if you are making more than one modification.

  6. In the left pane, click ProductStage in the Connections node.

    The properties and values for this connection object are displayed in the right pane. The value for the UDLPath property is C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl.

    click to expand
    Tip  

    To see the full path, expand the size of the Default Value column so that a scroll bar appears at the bottom of the right pane. Continue expanding the size of the default value column until you can use the scroll bar to view the full file path.

  7. In the right pane, double-click UDLPath.

    In this procedure, you will specify the Config.ini file as the source from which the UDLPath property will be retrieved at run time.

    click to expand
  8. In the Source list, select INI File, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, and then select SBS_OLAPUDLFilePath in the Key list.

    The Preview box displays the value stored in the Config.ini file for this key, and the Refresh button enables you to see its updated value if you change its value while configuring this assignment in the Dynamic Properties task.

    click to expand
  9. Click OK to save this assignment.

    You have successfully configured the Dynamic Properties task to dynamically configure the location of the UDL file used by the ProductStage connection object.

  10. Click SBS_OLAPAdditionalConnection in the left pane, and then double-click UDLPath in the right pane.

  11. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

    You have successfully configured the Dynamic Properties task to dynamically configure the location of the UDL file used by the SBS_OLAPAdditionalConnection connection object.

  12. Click NewProductsSource in the left pane, and then double-click DataSource in the right pane.

  13. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select Products in the Section list, and select NewProductData in the Key list.

    Notice that the path specified for NewProducts.txt file is C:\Microsoft Press\SQL DTS SBS\DataMovementApplication. However, the NewProducts.txt file you have been using for testing is located in the C:\Microsoft Press\SQL DTS SBS\Ch4\ChapterFiles folder. This will introduce an intentional error to demonstrate the functionality of the initialization file.

  14. Click OK and then click Close.

    You have successfully configured the Dynamic Properties task to dynamically configure the location of the delimited text file used by the NewProductsSource connection object.

    click to expand
  15. Click OK to save the Dynamic Properties task.

Next you need to configure the UpdateProductDim package to execute the SetPropertiesFromInitializationFile step before it executes any other steps in the package.

Configure the SetPropertiesFromInitializationFile step to execute first

  1. On the design sheet, select the SetPropertiesFromInitializationFile step, and then hold down the Ctrl key while you click the NewProductsSource connection object.

    Tip  

    You might want to rearrange the objects on the design sheet before you configure this precedence constraint.

  2. On the Workflow menu, click On Success.

    click to expand

You can now change the data sources and destinations used in the UpdateProductDim package without having to edit the package directly. At run time, the UpdateProductDim package retrieves information from the Config.ini file and uses this information during execution. You are now ready to perform similar steps in the UpdateCustomerDim package.

Configure a Dynamic Properties task in the UpdateCustomerDim package

  1. Switch to the UpdateCustomerDim package in DTS Designer.

  2. On the Task menu, click Dynamic Properties task and then type Properties From INI File in the Description box.

  3. Click Add to select a property to be dynamically set.

  4. Select the Leave This Dialog Box Open After Adding A Setting check box.

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

  6. In the right pane, double-click UDLPath.

  7. In the Source list, select INI File, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  8. Click SBS_OLAPAdditionalConnection in the left pane, and then double-click UDLPath in the right pane.

  9. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  10. Click NewCustomersSource in the left pane, and then double-click DataSource in the right pane.

  11. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, click Customers in the Section list, and then click NewCustomerData in the Key list.

    Notice that the path specified for NewCustomers.txt file is C:\Microsoft Press\SQL DTS SBS\DataMovementApplication. However, the NewCustomers.txt file you have been using for testing is located in the C:\Microsoft Press\SQL DTS SBS\Ch4\ChapterFiles folder. This will introduce an intentional error to demonstrate the functionality of the initialization file.

  12. Click OK, click Close, and then click OK again to save the Dynamic Properties task.

Next you need to configure the UpdateCustomerDim package to execute the Properties From INI File step before it executes any other steps in the package.

Configure the Properties From INI File step to execute first

  1. On the design sheet, select the Properties From INI File step, and then hold down the Ctrl key while you click the NewCustomersSource connection object.

  2. On the Workflow menu, click On Success.

You can now change the data sources and destinations used in the UpdateCustomerDim package without having to edit the package directly. You are now ready to perform similar steps in the LoadHistoricalData package.

Configure a Dynamic Properties task in the LoadHistoricalData package

  1. Switch to the LoadHistoricalData package in DTS Designer.

  2. On the Task menu, click Dynamic Properties task, type Properties From INI File in the Description box, and then click Add.

  3. Select the Leave This Dialog Box Open After Adding A Setting check box.

  4. In the left pane, expand Connections and then click ProductsSource.

  5. In the right pane, double-click UDLPath.

  6. In the Source list, select INI File, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLTPUDLFilePath in the Key list, and then click OK.

    Tip  

    Take the extra time to make sure you select the correct Key value in the Config.ini file. Debugging errors later will consume much more time.

  7. Click CustomersSource in the left pane, and then double-click UDLPath in the right pane.

  8. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLTPUDLFilePath in the Key list, and then click OK.

  9. Click ProductsDestination in the left pane, and then double-click UDLPath in the right pane.

  10. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  11. Click CustomersDestination in the left pane, and then double-click UDLPath in the right pane.

  12. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  13. Click 1996DataDestination in the left pane, and then double-click UDLPath in the right pane.

  14. Select INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  15. Click 1997DataDestination in the left pane, and then double-click UDLPath in the right pane.

  16. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  17. Click SBS_OLAPAdditionalConnection in the left pane, and then double-click UDLPath in the right pane.

  18. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  19. Click SalesFactDataDestination in the left pane, and then double- click UDLPath in the right pane.

  20. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select UDL in the Section list, select SBS_OLAPUDLFilePath in the Key list, and then click OK.

  21. Expand Task, click DTSTask_DTSBulkInsertTask_1 in the left pane, and then double-click DataFile in the right pane.

    The data source for the Bulk Insert task is configured within the task itself; it does not have a separate connection object. As a result, to enable a Bulk Insert task to be configured dynamically, you must configure the task to read its data source from the initialization file.

    click to expand
  22. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select HistoricalData in the Section list, select 1996Data in the Key list, and then click OK.

  23. Double-click FormatFile in the right pane.

  24. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select HistoricalData in the Section list, select FormatFile in the Key list, and then click OK.

  25. Click DTSTask_DTSBulkInsertTask_2 in the left pane, and then double-click DataFile in the right pane.

  26. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select HistoricalData in the Section list, select 1997Data in the Key list, and then click OK.

  27. Double-click FormatFile in the right pane.

  28. Click INI File in the Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\Config.ini in the File box, select HistoricalData in the Section list, select FormatFile in the Key list, and then click OK.

  29. Click Close and then click OK to save the Dynamic Properties task.

Now you need to configure the LoadHistoricalData package to execute the Properties From INI File step before it executes any other steps in the package.

Configure the Properties From INI File step to execute first

  1. On the design sheet, select the Properties From INI File step, and then hold down the Ctrl key while you click the TruncateData step.

  2. On the Workflow menu, click On Success.

  3. On the toolbar, click Save.

You can now change the data sources and destinations used in the LoadHistoricalData package without having to edit the package.

Test the LoadHistoricalData , UpdateCustomerDim , and UpdateProductDim packages

  1. On the DTS Designer toolbar for the LoadHistoricalData package, click Execute.

    The LoadHistoricalData package executes successfully.

  2. Click OK and then click Done.

  3. On the toolbar, click Save and then close the LoadHistoricalData package in DTS Designer.

  4. On the DTS Designer toolbar for the UpdateCustomerDim package, click Execute. One task failed during execution.

    click to expand
  5. Click OK.

    Notice that the Properties From INI File step succeeded, but the Load CustomerStage Table step failed.

  6. Double-click Load CustomerStage Table in the Status window.

    The Load CustomerStage Table step failed because it could not find the file specified in the initialization file.

    click to expand
  7. Click OK and then click Done.

  8. Switch to Windows Explorer and then navigate to C:\Microsoft Press\SQL DTS SBS\Ch4\ChapterFiles.

  9. Select and then copy the NewCustomers.txt and NewProducts.txt files.

  10. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication and paste these two files into this folder.

  11. Switch to the UpdateCustomerDim package in DTS Designer and then click Execute on the toolbar.

    A task in the package still fails.

  12. Click OK and then double-click Load CustomerStage Table in the Status window.

    The Load CustomerStage Table step failed due to the errors you introduced in the NewCustomers.txt file in the previous chapter, which means that the initialization file is pointing the Load CustomerStage Table step to the file that you just moved into the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication folder.

    click to expand
  13. Click OK and then click Done.

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

    The UpdateProductDim package executes successfully because you moved the NewProducts.txt file to the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication, which is where the initialization file configured the Load ProductStage Table step to look for the file.

  15. Click OK and then click Done.

You have now verified that the initialization file works properly and that you can use it to change the data sources used by the data movement application to load data. Obviously, the schema for the data sources cannot change or the packages will fail. Now that you have learned to configure a Dynamic Properties task to read information from a configuration file, you will learn how to configure the Dynamic Properties task to read values from global variables .

Configure a Dynamic Properties task to use global variable values

  1. In the UpdateProductDim package, double-click the Properties From INI File step on the design sheet and then click Add.

  2. Select the Leave This Dialog Box Open After Adding A Setting check box.

  3. Expand Tasks in the left pane, and then click DTSTask_DTSDataPumpTask_1.

    The DTSTask_DTSDataPumpTask_1 task is the task described on the design sheet as the Load ProductStage Table task.

    click to expand
  4. In the right pane, double-click MaximumErrorCount.

  5. In the Source list, click Global Variable to configure the MaximumErrorCount property to be set at run time based on the value of a global variable.

    click to expand
  6. Click Create Global Variables.

    Because you have not previously created a global variable to hold this value, you will create a global variable and then specify its default value.

  7. Type giMaxErrorCount in the Name box, select Integer (1 byte) in the Type list, type 5 in the Value box, and then click OK.

    Important  

    Global variable names are case-sensitive, so when you create them, pay attention to case. Establishing a naming convention that you follow for all global variables can be helpful.

    Now that you have added this global variable to the UpdateProductDim package, you need to map this variable to the MaximumErrorCount property.

    Tip  

    The prefix used in naming this global variable will be used in an ActiveX script in Chapter 7 to identify the type of data stored in this variable: g stands for global variable, and i stands for integer data type.

  8. In the Variable list, select giMaxErrorCount.

    click to expand
  9. Click OK to save this assignment.

  10. In the right pane, double-click InsertCommitSize.

  11. In the Source list, click Global Variable, and then click Create Global Variables.

  12. Type giBatchSize in the Name column, select Integer (small) in the Type list, type 500 in the Value column, and then click OK.

  13. In the Variable list, select giBatchSize, and then click OK to save this assignment.

  14. Click Close and then click OK to save these changes to the Dynamic Properties task.

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

    You have successfully configured the Dynamic Properties task to read configuration values at run time from two different data sources.

  16. In the UpdateCustomerDim package in DTS Designer, double-click the Properties From INI File step on the design sheet and then click Add.

  17. Select the Leave This Dialog Box Open After Adding A Setting check box.

  18. Expand Tasks in the left pane and then click DTSTask_DTSDataPumpTask_1.

  19. In the right pane, double-click MaximumErrorCount.

  20. In the Source list, click Global Variable, and then click Create Global Variables.

  21. Type giMaxErrorCount in the Name box, select Integer(1 byte) in the Type list, type 5 in the Value box, and then click OK.

  22. In the Variable list, select giMaxErrorCount, and then click OK.

  23. In the right pane, double-click InsertCommitSize.

  24. In the Source list, click Global Variable, and then click Create Global Variables.

  25. Type giBatchSize in the Name box, select Integer(small) in the Type list, type 500 in the Value box, and then click OK.

  26. In the Variable list, select giBatchSize, and then click OK.

  27. Click Close and then click OK again to save this change to the Dynamic Properties task.

  28. On the toolbar, click Execute.

    The Load CustomerStage Table step processed all 29 rows because there were only two errors in the NewCustomers.txt file and the maximum error count was set to 5.

  29. Click OK and then click Done.

  30. On the toolbar, click Save, and then close the UpdateCustomerDim package in DTS Designer.

You have now configured the UpdateProductDim and UpdateCustomerDim packages to support the dynamic configuration of the maximum number of errors permitted before a Transfer Data task terminates, and also the number of rows that will be committed in a single batch.




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