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:
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
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
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"
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