Parameterized DTS Packages
A natural thing to want to do with a DTS package is to parameterize it so that, for example, it can work with different data sources/destinations than the ones it referenced when it was originally built. Things like global variable values, connection properties, object names, and the like are natural targets for parameterization.
There are a couple of ways to parameterize DTS packages. The first is to use a Dynamic Properties task. A Dynamic Properties task allows you to set the value of any property in a package from one of six sources:
A global variable from the package
A value in an INI file
An environment variable
A T-SQL query (only the first column of the first row in the query result set is used)
A data file
A constant
Given that you can supply values for global variables when you execute a package (e.g., using the dtsrunui utility or an ExecutePackage task within another package), a common practice is to assign global variables to package properties using a Dynamic Properties task, then assign those variables' values at runtime. Using global variables in this way provides a layer of abstraction between the mechanism supplying the parameter values and the dynamic method used to assign property values within the package. This allows you to easily change the method of executing the package without losing the dynamic nature of the package.
Another way to parameterize a DTS package is via ActiveX script code. It's often less trouble to modify package properties using script code than it is to set up a Dynamic Properties task. Also, understand that you can't insert global variables into the middle of a property using a Dynamic Properties task—either you assign the whole property or you don't assign any of it. Using script code or Automation code from outside the package, you have more control over changing property values at runtime and can use global variables to help you do so in any way you see fit.
Exercise 20.1 A Parameterized Package
To gain a better understanding of how a package can be parameterized, load the ParamExample.DTS package from the CH20 folder on the CD accompanying this book into the DTS Designer. This package will copy a table of your choosing from a specified source server and database to a specified destination server and database.
1. | Double-click the Dynamic Properties task named Get Params. |
2. | Double-click each of the entries in the Change list to see what properties they're being used to assign. What you should see is that global variables are being used to assign the key connection properties for the TransferObject task. |
3. | Exit the Dynamic Properties dialog and right-click the DTS Designer canvas. Select Package Properties from the content menu. |
4. | Click the Global Variables tab in the property dialog. There you should see six global variables defined: one each for the source server, database, and table and the destination server, database, and table. |
5. | Start the dtsrunui utility. In the DTS Run dialog, change the Location field to Structured Storage File, then select the ParamExample.DTS package that you previously viewed in the DTS Designer by clicking the ellipsis button to the right of the File name: text box. |
6. | Key ParamExample for the package name, then click the Advanced button to display a dialog that will allow you to set global variable values for the package before executing it. |
7. | Configure values for the source server, database, and table as well as the destination server, database, and table. The pubs and Northwind databases are fine for use here. |
8. | Click OK to close the Advanced DTS Run dialog, then click the Run button to run the package with your parameterized values. You should see the package run and copy the specified object from the source to the destination you specified. |