Introduction

Have you ever faced the situation where you have needed to move a DTS

package from one server to another, say from development to production?

The typical approach might be to save it as a file from your development

server, then connect to production, open the file, modify the database

connection, and then save it on production. This works fairly well

assuming you are only moving one or two packages and you have access to the

production database server. But if you need to move multiple packages

across multiple environments, this will get tedious very quickly. It

can also be error prone. For example, you can miss changing a

connection, or the transformations can be inadvertently reset as the package

is edited.

Running a package from the command line

For our discussion, let’s assume you have a straightforward package to

extract some data from your database based on a query, as shown in Figure 1

below. However the approach described will also work for nearly any

activity using a DTS package, such as importing data from a file or moving

data between databases.

Figure 1. A typical DTS package

Connection 1 is the database connection (source) and Connection 2 is the

destination, in this case a text file. 

The first question to address is: Can we avoid the need to save the package

on different servers (development, QA, production)? Well, we can save

the package as a file. But don’t you still have to open the package

from Enterprise Manager to execute it? No. Microsoft provides a

command line utility to run a DTS package. It is called dtsrun.exe.

Dtsrun.exe accepts a file name and package name as arguments. So you

can enter:

dtsrun /Fmydtspkg /Nmydtspkg

to run a package named mydtskpkg.dts.

Of course, we still have one major problem to overcome: the package is still

executing against the database we created on.

Making the package portable

So, how do we deal with the fact that the server name and database name are

in effect hard coded in the package? The DTS editor provides the

“Dynamic Properties Task”. Add a Dynamic Properties Task to the

package. The properties window for it will appear. Type in a

description, such as “Set Data Source”, and then click the “Add…” button.

Open the tree to Connections-Connection 1-Data Source. Click the

checkbox “Leave this dialog box open after adding a setting”, then click the

Set… button.

Figure 2.Set the Data Source to a Global Variable

In the next dialog box, set source to Global Variable and then click the

Create Global Variables… button. Enter a Name, leave the type as

String, and enter a default value.

Now choose the variable that you just created.

Repeat the process described for any other properties that you want to

change, such as Initial Catalog (the database name) and User ID and Password

if you are not using integrated security. If you are extracting to a

text file, the Data Source for that connection will be the filename.

Important: Now that you have added the Dynamic Properties task, make sure it

is the first task to execute by adding a “On Success” workflow between it

and Connection 1. If you don’t do this, the job will fail because the

values are not yet set when it starts to execute the extraction step.

Your DTS package should now look something like:

Figure 3. A DTS package with the Set Data Source task

At this point, save the package and execute a test run of the package from

Enterprise Manager to confirm that the changes made have been successful.

Setting variables from the command line

As you recall from the first section, we can run a DTS package from the

command line using the dtsrun utility. But how do we set the global

variables? To do this use the /A switch. For example,

dtsrun /Fmydtspkg /Nmydtspkg /A”Server:8=devserver”

will set the global variable Server to devserver. The :8 is required

to indicated that data type is string.

Tip: The global variable names are case-sensitive. Make sure

you exactly match the name in your command line with the name used in the

package. If they don’t match, no error is reported, but the command

line setting is ignored and the default value set in the package is used

instead.

Putting it all together

Now that we have the building blocks, let’s build a simple batch file to run

any dts package. I will call it rundts.bat. 

rundts.bat

Edit the values in the four set statements accordingly to reflect your

server name, database name, directory for the extracted data, and directory

for the extract log. The extract will use the same filename as the DTS

package, but with a .txt extension. Setting the /W flag to TRUE in the

CALL dtsrun line indicates to log the output to the event viewer.

There are also two flags that rundts.bat accepts. The first indicates

whether to start notepad and open the output file after each step. The

second flag determines whether to pause between each step. This allows

the execution to be monitored or to run unattended.

So if you need to run three DTS packages, you can create another batch file

as:

CALL RUNDTS extract1 Y Y

CALL RUNDTS extract2 Y Y

CALL RUNDTS extract3 Y N

This will pause processing between each extract and open the output file for

review.

Conclusion

This article provided a straightforward approach to make DTS packages

portable between servers or databases. By leveraging the SQL Server

2000 Dynamic Properties Task and the ability to run packages from the

command line, the package can be migrated with almost no effort. 

Of course, what is presented is just a starting point, but the general

technique can modified to meet many needs.