Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


SQL Server DTS command line utility

By:   |   Read Comments (2)   |   Related Tips: More > Data Transformation Services

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.

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.

To use this tool do the following:

  • 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

  • DTSRun /S "(local)" /N "Test Package" /G "{9EA5A37A-40F6-4F35-B901-68A2E627C6B0}" /A "Global_var1":"22"="24" /A "Global_var2":"8"="TEST" /W "0" /E

Here is the same exact command using the encrypted option

  • DTSRun /~Z0x170739A6D25F8634A01E5496323EC6C60EB61731B03AC48B079

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

Last Update:

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Wednesday, August 01, 2012 - 4:02:33 AM - Sonny Back To Top



Kindly help me resolve my issue regarding on scheduling of DTS package. If I run the package it  ran successfully. But if I scheduled it it always run failed.

Any idea on how to resolve this issue?

By the way i'M using SQL server 2000.






Tuesday, July 17, 2012 - 10:29:36 AM - Dinesh Back To Top

Hi Greg,


Thanks for the wonderful article, it really educated me lot.


Thanks again!!!




Learn more about SQL Server tools