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.