Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Package execution differences between DTS and SSIS


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

Problem
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.

Solution
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

1. DTSRUNUI

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:

DTSRUNUI



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.

DTSRUNUI-ADVANCED


2. DTSRUN

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

DTSRUN-Example

 

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.


1. DTSEXECUI

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

DTSEXECUI

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:


DTSEXECUI-Command Line statement


2. DTEXEC

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:

DTEXEC-Example


Next Steps



Last Update:






About the author





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 


SQL tips:

*Enter Code refresh code     



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

Hi,

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

 Thanks,

Rajesh


Learn more about SQL Server tools