Setting DTS Package Properties at Runtime
October 31, 2005
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.