Portable DTS Packages

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.

ECHO Please provide a filename
without DTS extension, followed by Y to show output, and another Y to pause
before returning

:end

TIME /T

ECHO %1 Completed

@ECHO ON

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.