Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

SQL Server DTS command line utility

MSSQLTips author Greg Robidoux By:   |   Read Comments (2)   |   Related Tips: 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.

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
    E54A326246C8B7F6CA00FB14DBC202B42BAB8A5329CBA9CDF46467DCB70
    42644B280C53D2FEC547CA05155AEA7E1D7AAB1B49DD0ED086F4FDBB021
    62BD32660D06B581E07FBE8C3EB3648D16B2EE9AE4C050ED59B73E781B610
    3CBEE6A8750B8A699B8D54B611AE9E4F7A1A238D22DC33DABEAAA1454A
    C0F2E54D926C8A843216100C9789242B1F48827BB48F8ED189EAAC9D5427C
    9F2559C081448924D2C590B73AB3F2F2AC4DA13ED3C5FF57A2DC14706CEE
    31D70AFA5BBB73CB70D9FD83918340D01F28BFE49CCBEA9C7A56E2D219B
    634DD6030F031F014A33EBA3F77785A1FE6B896068DFCEC350526E468235F
    8910F13CCDDAC1CFEB0EB53E787D4586A874B2437B4DB2AD6A0DE7CD82
    27D8593D010708C97A1FA2EB45E

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: 6/26/2006


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, July 17, 2012 - 10:29:36 AM - Dinesh Read The Tip

Hi Greg,

 

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

 

Thanks again!!!

 

Cheers

 


Wednesday, August 01, 2012 - 4:02:33 AM - Sonny Read The Tip

 

Hi,

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.

Thanks,

Sonny

 

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.