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

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.

Dynamic Properties Task

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.

Dynamic Properties

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.

Package Properties of Dynamic Properties task

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.

{mospagebreak title=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.

Establishing a text connection

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.

SELECT LastName, FirstName, HireDate, City, PostalCode
FROM Employees

If you were to run this package as shown now, 

Package execution

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

Result of executing the package

 

{mospagebreak title=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.

A typical INI file

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

C:Documents and Settingscomputer userDesktopNov7
DTS_DynPropsWriteToTex.ini

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.

Destination properties, file name

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.

Using INI file to assign run time values

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.

Run time values assigned

{mospagebreak title=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.

 

Succesful run of package

 

Result of execution

 

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. 

6 thoughts on “Using Data Transformation Services: How to Configure a Dynamic Property Task

  1. DTS’s dynamic property task is one of the most useful tasks as it empowers you with capability to exercise control at run time. The example shown is a simple one, but the principle is the same. Other DTS related tutorials may be found at ASPFREE under MS SQL Server. I recommend reviewing them as well to get a full picture of DTS. Enjoy the tutorial and if you have questions please do not hesitate to ask.

    cordially yours,

    Jayaram Krishnaswamy

  2. Hello Jayaram,

    I have a question on this subject. I currently have a DTS package that contains about 30 SQL Tasks which kick off sequentially using the workflow options inside the pkg. Now if I want to convert these sql tasks to be dynamic, there will be two icons for each of the tasks. So how I do create the workflow in this scenario. Appreciate your thoughts. Thanks.

    Cheers,Padhu

  3. Can you be more sepcific as to what this package does? Are these 20 tasks inter related in some way? The article is just a simple example for the readers to see waht a dynamic task does, but is not an elaborate and comprehensive one.

    Jayaram Krishnaswamy

  4. Hi,
    Basically I have SQL based web reports saved in 30 different sql files. Each sql file runs the query and generates html reports. So right now inside the pkg sql-1 task1 will run..on completion sql task2 will be run and so on…These tasks can’t be combined into a single giant task. The challenge is everytime the sql query code changes I have to open each and every sql task and copy and paste the new code. Hence I was thinking about the dynamic sql tasks where I have just a placeholder and use the datafile as the source which will point to the sql files…so everytime my sql query changes I don’t have to go into each of the 30 tasks and copy and paste the changes.
    Thanks,Padhu

  5. Send me some more info. Especially if you have screen shot of your package in the dts design editor. From what I understand everytime the report content changes because your query is changing. For this query, I assume you are using ExecuteSQL task. Then you are using workflow to make them go one after another. May be you should use the SQL Job to schedule the tasks. I am not sure how much this is useful to you. Understanding the problem is 95% of the solution.

  6. Hi

    I have just started using the Dynamic Properties task and found it to be a very helpful component of DTS. Is it possible to use a Global variable in the Dynamic Properties Task (Add/Edit Assignment) while using the Query Source.

    Eg. Select SQLStatement from tblRule where RulePK = ?

    It doesn’t seem to like the use of parameters here. I am trying to set the Source and destination SQL Statements of a Transform task dynamically.

    Any help would be appreciated.

    Thank You.

[gp-comments width="770" linklove="off" ]