Setting DTS Package Properties at Runtime - Page 3

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 define one.

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.

Using Constants

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 parameter.

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 parameter normally.

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.

Conclusion

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