Setting DTS Package Properties at Runtime

Wouldn’t it be nice if you could change DTS package
properties at runtime? If you could then you could have a generic package that
processed different files, loaded different database tables and/or connected to
different servers, plus a slew of other things. In this article, I will be
discussing how to use the dynamic property task to set DTS package properties
at runtime.

The dynamic properties task is a task when added to a DTS
package has the capability of changing any package property. You can use the
dynamic properties task to change package properties using six different
methods. These methods are: ini file, query, global variable, environment
variable, data file or constant. I will show you how to using each one of
these methods. But first I need to build a small DTS package and review how to
identify the package parameters that will be changed at runtime using the
dynamic properties task.

Demonstration Package and Identifying Package Properties

My demonstration DTS packaged is named "Dynamic
Parameters." This package takes data from a SQL Server table and creates
a comma delimited output text file. Here is what the package looks like:

This package is quite simple. All it does is create a flat
text file named "Author File" from a table on the database pointed to
by the SERVER connection. Now to dynamically set the parameters of this
package I need to add a dynamic properties task to this package and make it the
first task to be executed. Once I have done this my package looks like this:

Once the dynamic properties task has been added to my
package I will need to identify the parameters I what this task to change. To
do this I right click on the dynamic properties task, and then click on properties.
This displays the following screen:

On this screen, I will need to click on the "Add"
button to identify the properties I want to set dynamically. Each time I click
on the "Add" button allows me to add one package property to the "Change
list:". When I click on this button, the following screen will be
displayed:

On this screen, I can browse through the properties in my
DTS package by expanding the appropriate collections in the left hand pane.
One of the parameters that I want to change is the Data Source for the SERVER connection.
To do this I expand the "Connection" collection and drill down until
I find the Data Source property. After I have drilled down, the dynamic property
task screen looks like this:

Note I have clicked on the Value "Property Name" of
the "Data Source" parameter. This identifies the DTS Package parameter
I want to set dynamically. To identify the method I would like to use to set
this parameter, I click on the "Set" button. When I do this, the
following screen is displayed:

On this screen, I can use the pull down menu for "Source:"
to specify the method I would like to use to set my parameter. As I mentioned
before the options available are: .ini file, query, global variable or
environment variable, data file, or constant. Depending on what option I pick
for Source, I get a different "Source Information" display to
identify where to get the value for the dynamic parameter. I will show you
each of the Source Information displays as I discuss each of the different
sources.

Basically, this is how to identify what DTS package parameters
will be dynamically set. I would have to go through this for each of the
properties I wanted to dynamically set. For my simple package, I plan to
dynamically change the Data Source parameter of the SERVER and the AuthorFile
connection. Now let’s go through setting one or both of these package
properties using each of the different sources. Keep in mind you can only use a
single source to set any one of the DTS package parameters, although if you are
setting multiple package parameters you can mix and match the sources you use
for setting the different parameters.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles