SQL Server DTS command line utility

By: Greg Robidoux   |   Comments (2)   |   Related: More > Data Transformation Services

Problem

Have you ever wanted to or needed to run a DTS package from outside of SQL Server?  This could be from an application you develop or even just from a command line.  Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in your applications, but in order to get this to work properly you need to construct the DTS command correctly.  This could be pretty tricky unless you are very familiar with the command syntax.  Luckily Microsoft included a tool to allow you to construct the command using a GUI.

Solution

SQL Server includes a utility called DTSRUNUI.exe.  This application can be run from a command line or by just simply double clicking on the application file.  This file can be found in the following directory: C:\Program Files\Microsoft SQL Server\80\Tools\Binn or wherever you installed the SQL Server tools.  The following screen comes up when you launch this program.

To use this tool you can either use a DTS package that exists within SQL Sever, Meta Data Services or a Structured Storage File.  Not sure if you have noticed or not, but when saving your DTS package you have the option to save it externally as a Structure Storage File or within SQL Server and here is one of the reasons why.

• select location of where the DTS package is stored (SQL Server, Meta Data Services or Structured Storage File)
• select the Package name (click on ...)
• select the authentication mode for running the DTS package on the bottom part of the screen
• click the Advanced button (this is where you can generate the command line syntax)
• when you click Advanced the following screen comes up

• on this screen you can set several options
• if you are using variables, select the variable to use, the type and the run value.  Do this for each variable of your DTS package
• you can write out information to a Log File or to the Event Log whenever this gets run
• you can encrypt the command, so user IDs, passwords etc... can not be seen
• when you have all your options set, click Generate to create the command
• copy and paste this command into your application

The command presents several different parameters

• /S - SQL Server to run command on
• /N - DTS package name
• /U - SQL Server login
• /P - SQL Server password
• /E - uses windows authentication
• /G - package guid string (this is the unique identifier for the DTS package)
• /V - package version guid string (in case you want to run an specific version of the package)
• /M - package password (if DTS package has a password)
• /F - filename (if using a Structured Storage File)
• /R - repository database name (if other then the MSDB database)
• /A - variable passed to the DTS package (this part gets tricky, so using this tool makes it much easier)
• /L - log file name (if this option is used)
• /W - NT event log completion status
• /Z - encrypted command

Here is the command with the two variables from the example above using windows authentication

Here is the same exact command using the encrypted option

Next Steps

• If there is a need to run a DTS package from outside of SQL Server, use this tool to construct the command syntax
• Determine if it makes more sense to run your DTS packages internally or externally
• If you need to encrypt your DTSRUN commands, use this tool
• For this or other DTS commands, refer to SQL Server Books Online
• Add this tool to your SQL Server toolbox

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips