Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 31, 2005

Setting DTS Package Properties at Runtime - Page 3

By Gregory A. Larsen

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.


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM