Setting DTS Package Properties at Runtime - Page 2

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.

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.