Using Environment Variables
Another way to dynamically change DTS package parameters is
to set them using Environment Variables. Typically, there are many environment
variables already assigned on a machine that might be useful. Although if
needed, additional environment variables can be created. Like with the other
methods, when I change the "Source" pulldown to "Environment
Variable" I am able to select an environment variable using the pulldown
in the "Variable" field. Below I have used this pulldown to display
some of the environment variables on my machine.
As you can see above, my machine already has an environment
variable called "COMPUTERNAME", which will be useful in setting the
data source parameter of the SERVER connection in my package. If I want to
use an environment variable to set my AuthorFile connection, I will need to
One way to define an environment variable is to go to the "Advanced
Tab" under "System" from the "Control Panel" menu. Under
that tab there is a button labeled "Environment Variables." When you
click on this button, a screen will be displayed that allows you to create user
or system environment variables. You can only create system environment
variables if you are logged on with Administrator rights.
You can also assign constants to a DTS package property. To
do that just select "Constant" from the source pulldown. Upon doing
that the following screen will be displayed.
On this screen just enter the constant you would like to
use. The following screen shows a constant for the AuthorFile data source
Here you can see I specified a constant of "c:\temp\Author_File.txt."
I have yet to find a use for using a constant. I figure if a parameter is
going to be set dynamically then why use a constant, over setting the DTS
Using a Data File
Like using an ".ini" file to set DTS package
parameters, you can also assign parameters dynamically from a text file. When
using a Data File, the whole data file is used to identify the value of the DTS
package parameter. Below is a screen shot that shows how I set the AuthorFile
DTS package parameter from a text file named "C:\temp\AuthorFile_Parm.txt."
Using a different data file on each server, with a different
value allows you to easily change your DTS parameters as you move your DTS
packages between your different SQL Server machines.
As you can see, there are a number of different ways to set
your DTS package parameters using the Dynamic Properties Task. Most of these
methods are useful for changing your DTS package parameters as you migrate your
packages from one server to the next in your environment. Rather than manually
change your DTS package parameters as you migrate from one environment to
another you should consider using one of the above methods to make your DTS
package more dynamic and easier to migrate between servers.
See All Articles by Columnist Gregory A. Larsen