Package execution differences between DTS and SSIS

By: MSSQLTips   |   Comments (1)   |   Related: 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:

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)

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:

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:

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]

Next Steps

• Review your current method for executing DTS/SSIS packages
• Work with these two tools to gain a better understanding of the differences in GUI options and syntax for command-line execution
• Refer to SQL Server Books On Line for SQL Server 2000 and 2005 for additional information on these tools
• Review these other DTS and SSIS related tips
  • SQL Server 2000's DTS Authentication Alternative = UDL Files
  • SQL Server DTS command line utility
  • SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package
  • SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)

About the author

MSSQLTips.com 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