Setting DTS Package Properties at Runtime

Monday Oct 31st 2005 by Gregory A. Larsen
Share:

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, Gregory Larsen discusses how to use the dynamic property task to set 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:

Click for larger image

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.

Using An ".ini" File

To use an ".ini" file for setting my DTS package parameters I will first need to build an ".ini" file. What I normally do is have a single ".ini" file for all my packages on a given server. Then inside the ".ini" file, I define a different section for each package. Here is the section definition from inside my ".ini" file for the simple package I am using in this article:

[Dynamic Parameters]
OUTFILE_DATASOURCE=c:\temp\SERVER1AuthorFile.txt
SERVER_DATASOURCE=SERVER1

As you can see, I have a key defined for each data source. Now since I plan to migrate this package to different servers I will have a different ".ini" file on each server. Therefore, by having a different ".ini" file on each server, I can change the key settings to map my DTS parameters to the appropriate place for any given server. This allows me an easy way to move packages from one server to another without having to modify my DTS package.

I will need to add two dynamic parameters for each Data Source parameter, one for each of my connections. To set my DTS package parameters I will use the "Add" button on the "Dynamic Properties Task Properties" screen, then select the appropriate Data Source value parameter, and click on the "Set button." This brings up the "Add/Edit Assignment" screen. On this script, I can browse the system for my ".ini" file by clicking on the "…" button. Once I have found and selected the appropriate ".ini" file, I then can select the correct "Section," and "Key" values using the pulldown menus. After I have set the "Source Information" to the correct key, I click on the "OK" button to assign the selected key to my DTS package parameter. Below are two screen shots that show how I set the "Source information" for each one of my connection Data Source properties.

Setting for SERVER Data Source:

Setting for AuthorFile Data Source:

Once I have made both of my dynamic properties assignments, my Dynamic Properties window looks like below:

To finalize the setting of my dynamic parameters all I need to do is click on the "OK" button. One of the benefits of using an ".ini" file is the fact that you change the DTS parameters by just changing the keys in the ".ini", without changing the DTS package itself.

Using a Query

I can use the dynamic properties task to set my DTS package properties with a T-SQL Query. You identify the specific DTS parameter you want to set, then when adding the parameter assignment, you specify "Query" for the "Source." After doing this the "Add/Edit Assignment" screen will allow you to identify the "Connection" and "Query" values. Here is a screen shot that shows how I set my AuthorFile DTS parameter:

In the "Connection" item, a connection needs to be identified that points to the server where the "Query" will be executed. In my case, I want to query the server identified in the "SERVER" connection. In the above example, I set the AuthorFile connection by selecting some information from a table that contains different parameters. To do this I built a SELECT statement that will get the appropriate DTS parameter from the "dba.dbo.dtsparm" table. To verify that I entered the correct SELECT statement, I clicked on the "Refresh" button to preview the value that will be selected. In my case, this is "c:\temp\Server1_Author.txt." Keep in mind that you may have some problems using the query method in setting your "Connection" item. This problem occurs if you do not have a connection defined that connects your DTS package to the server that is available from where you run the DTS package.

This method of replacing DTS package parameters works well, if you have a table on each of your servers where the DTS package will live, that is used to hold all your dynamic parameters. Plus, you have defined a connection for the "Local machine" that is used for the "Connection" information.

Using Global Variables

Another method to dynamically set DTS package parameters at run time is using global variables. To use this method I identify the DTS package parameters I want to set using a global variable, just like the other methods, by browsing through the list of package parameters then clicking on the "Set" button. When the "Add/Set Assignment" menu is displayed, I select "Global Variable" from the "Source" pulldown. After doing that, the following screen is displayed on my machine:

Since I do not have any global variables defined for my package, there are no variables identified in the "Variable" pulldown display. To create and associate a global variable with my selected DTS package parameter, I click on the "Create Global Variable" button. This allows me to create my global variable. Below I have created a global variable "AuthorFile", and set it to "c:\temp\Server1_Authorfile.txt".

When I click on the "OK" button I am taken back to the "Add/Edit Assignment" screen, where I can associate the global variable I just created with my "AuthorFile" DTS package parameter. Below I used the "Variable" pull down to select my newly created global variable, and then clicked on the "Refresh" button to preview the currently assigned value for the global variable.

One of the big advantages of using global variables is the "dtsrun" utility allows you to change the values of the global variables, when the utility runs the DTS package. The assignment of global variables is done by using the "/A" arguments on the "dtsrun" utility. So if you have SQL Agent jobs or batch scripts that issue the "dtsrun" command on your different servers, then you can set these global variables appropriately on each server using the "/A" arguments. This allows you an easy method of changing the DTS package parameters for each server in which you run the package.

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.

Click for larger image

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved