Package execution differences between DTS and SSIS

By:   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | More > Integration Services Execute Package Options


With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.


In SQL Server 2000 and 2005 there are a number of ways to execute a package outside of Enterprise Manager and Query Analyzer:

SQL Server 2000


Type dtsrunui from either a command prompt or Run dialog box, which opens the following GUI interface:


This interface gives you the option of executing a package from a SQL Server stored file (in msdb), a Meta Data Services file, or a Structured Storage File (*.dts stored in the File System). If you choose SQL Server or Meta Data Services as the source of the package and have a named instance, replace (local) with the <server_name>\<instance_name>.

You can also obtain the DTS command-line statement by clicking on the "Advanced" button next to the package name:


Then click "Generate" on the bottom right of the window to generate the DTS command. This can then be used to execute the DTS package from a command line.



Type dtsrun from a Command Prompt (can be run from any command prompt without having to navigate to a particular folder). A series of switches are available:

  • /S Server Name
  • /U User Name
  • /P Password
  • /E <Use trusted connection instead of /U /P>
  • /N Package Name
  • /M Package Password
  • /G Package GUID String
  • /V Package Version GUID String
  • /F Structured Storage UNC filename (overwritten if /S also specified)
  • /R Repository Database Name <uses default if blank; loads package from repository database>

Package operation (overrides stored Package settings):

  • /A Global Variable Name:typeid=Value <may quote entire string (including name:typeid)>
  • /L Log file name
  • /W Write Completion Status to Windows Event Log <True or False>

DTSRun action (default is to execute Package):

  • /!X <Do not execute; retrieves Package to /F filename>
  • /!D <Do not execute; drop package from SQL Server (cannot drop from Storage File)>
  • /!Y <Do not execute; output encrypted command line>
  • /!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)

Here is what is seen in the command prompt window when running a package using DTSRUN


SQL Server 2005

In SQL Server 2005 the GUI and command-line options for package execution are still available, but the syntax and switches have changed somewhat.


Type dtexecui from either a command prompt or Run dialog box, which opens the Execute Package GUI interface:


Note that there are many more options in this GUI compared with the one for SQL 2000. If you wish to copy the command-line statement to use in dtexec, click on Command Line section:

DTEXECUI-Command Line statement


Use dtexec from the Command Prompt by typing in dtsexec (can be run from any command prompt with having to navigate to a particular folder). A series of switches are available (the following information was obtained by typing dtexec /? in the command prompt):

  • /CheckF[ile] [Filespec]
  • /Checkp[ointing] [{On | Off}] (On is the default)
  • /Com[mandFile] Filespec
  • /Conf[igFile] Filespec
  • /Conn[ection] IDOrName;ConnectionString
  • /Cons[oleLog] [[DispOpts];[{E | I};List]]
  • DispOpts = any one or more of N, C, O, S, G, X, M, or T.
  • List = {EventName | SrcName | SrcGuid}[;List]
  • /De[crypt] Password
  • /DT[S] PackagePath
  • /F[ile] Filespec
  • /H[elp] [Option]
  • /L[ogger] ClassIDOrProgID;ConfigString
  • /M[axConcurrent] ConcurrentExecutables
  • /P[assword] Password
  • /Rem[ark] [Text]
  • /Rep[orting] Level[;EventGUIDOrName[;EventGUIDOrName[...]]
  • Level = N or V or any one or more of E, W, I, C, D, or P.
  • /Res[tart] [{Deny | Force | IfPossible}] (Force is the default)
  • /Set PropertyPath;Value
  • /Ser[ver] ServerInstance
  • /SQ[L] PackagePath
  • /Su[m]
  • /U[ser] User name
  • /Va[lidate]
  • /VerifyB[uild] Major[;Minor[;Build]]
  • /VerifyP[ackageid] PackageID
  • /VerifyS[igned]
  • /VerifyV[ersionid] VersionID
  • /W[arnAsError]

Here is what you will see when you run dtexec:

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

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

View all my tips

Comments For This Article

Wednesday, August 27, 2008 - 9:29:25 PM - rb_talk Back To Top (1709)


 There is a small typo error in this article. Instead of dtexecui.exe its mentioned as dtsexecui.exe.



get free sql tips
agree to terms