Using Data Transformation Services: How to Configure a Dynamic Property Task

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.

Package Description

Let's create a new package, a simple one which copies the result of a query of the SQL 2000 server database to a text file at some location on the hard drive of the machine where the server is located.

First establish a connection to the Northwind database on your Server (please refer to previous tutorials to do this task). Then create a file on the C:\ drive and configure the destination connection as shown in this picture.

With these two connections made, add a Transform Data Task that uses the query shown below to copy the result of the query to the text file. Working with the Transform Data Task has been clearly described in more than one tutorial. This query can be pasted or can be created using the Query designer.

If you were to run this package as shown now, 

you would see the following in the FileText.txt file.

Run time modification of package

Let's say that instead of being written to C:\FileText.txt we want the file to be written to a different file at run time. Furthermore, we want to indicate where to store the query result set using an INI file. The INI file for such a task is a simple text file, such as the one shown next.

This file is stored in some location, in this present tutorial at

The Dynamic Properties Task would look into this file at run time and find that the destination file to write to is C:\WriteToText.txt.  In order to configure the dynamic properties, add a Dynamic Properties Task, right click the icon, and then click on the button Add... as described previously. Now expand all the nodes in the tree as shown below.

As we are interested in changing the file name, click on the TextFile Destination node and highlight the DataSource PropertyName in the list view on the right hand side of the above picture. Now click on the Set... button to set this value. This takes us to the next screen, which is where you will be indicating that you want to use the file name stored in an INI file. If needed you may make multiple property assignments, that need to be changed during run time. However, you need to code the appropriate INI file.

The default source is INI file, but several others can be used as mentioned in the introduction. If you now click on the ellipsis button [...] on the same line as File: you can browse to the location where you saved your INI file. The next two drop downs, Section and Key will read from your INI file as shown below.

Running the package

Now you may click OK to this screen followed by another OK to the initial screen which follows. With this the Dynamic Properties Task is configured. All that needs to be done is to run the package. While running the package do not highlight any of the objects in the designer, but execute the package from the Menu item Package. The package execution results show a success and you will find the text file in the C:\ drive as shown.

Summary

In this tutorial, the Dynamic Properties Task of the Data Transformation Services was described. This is best suited for you if you are likely to determine what values are going to be used, such as a server name, a name and location of a file, and so on.  Using the Dynamic Properties Task, a very large number of properties can be modified at run time. However, due to roll back considerations the dynamic properties task is not suited for transactional processing.

A very simple example was chosen to show how the configuration is carried out using the DTS designer interface to demonstrate the basic steps. In practice it is never this simple, nor does it typically involve only a single step.