Using Data Transformation Services: How to Configure a Dynamic Property Task
(Page 1 of 4 )
This tutorial describes the Dynamic Properties Task of the Data Transformation Services. Using the Dynamic Properties Task, a very large number of properties can be modified at run time. This will be illustrated with a simple example.
Introduction
The dynamic properties task is one of several tasks in the Data Transformation Services that first became available with SQL 2000 Server. This task allows you to modify any of the properties of a package at run time using resources outside the task. In this the Dynamic Properties Task appears more like a job. This is very useful since the properties defined using the DTS Designer or the Import/Export wizard are fixed at design time.
The runtime modification needed may be stored in an external file and called during package execution -- a nice feature to have, by all means. This task is explained in some detail with one example. However, an even better method for run time behavior than the dynamic properties task is coding the task.
Dynamic Properties Task
Let's take a look at the Dynamic Properties Task. In the Enterprise Manager go down to the node Data Transformation Services and expand the node to access the Local Packages. Right click and choose a new package, which opens up the DTS Designer. Save it, and provide a name for the package. Currently there are no objects in the package. Go to the Task menu item, and add a Dynamic Properties Task. You will see the following icon in the designer pane.
You can access the properties by right clicking this icon, which brings us to the next screen, which explains what this task does. The run time behavior of a package can be changed by using a INI file, the result of an SQL query, a global variable, a datafile, an environmental variable or a constant.
Since there are no objects as yet this screen is empty. Click on the button Add... to open up the screen shown in the next picture. The picture shows all the nodes in the treeview completely expanded. Right now only the current task properties with their default values are shown. These may also be changed, if needed.
This shows that you can modify just about any property that is accessible to the nodes of the tree, which is pretty much everything. You can set the properties that you want to use at run time for connections, tasks, steps, Global variables, and so on.
Next, I'll show you an example.
Next: Package Description >>
More Database Articles
More By Jayaram Krishnaswamy
