DTEXEC command line utility

SQL Server includes the command line tool DTEXEC.EXE which can be used to execute an SSIS package.  DTEXEC can be run from a Command Prompt or from a batch (.BAT) file.

The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.

Note:When you use the version of the dtexec utility that comes with SQL Server 2008 to run a SQL Server 2005 Integration Services (SSIS) package, Integration Services temporarily upgrades the package to SQL Server 2008 Integration Services (SSIS). However, you cannot use the dtexec utility to save these upgraded changes.

dtexec (SSIS Tool): Phases of Execution

• The utility has four phases that it proceeds through as it executes. The phases are as follows:
• Command sourcing phase: The command prompt reads the list of options and arguments that have been specified. All subsequent phases are skipped if a /? or /HELP option is encountered.
• Package load phase: The package specified by the /SQL, /FILE, or /DTS option is loaded.
• Configuration phase: Options are processed in this order:
• Options that set package flags, variables, and properties.
• Options that verify the package version and build.
• Options that configure the run-time behavior of the utility, such as reporting.
• Validation and execution phase: The package is run, or validated without running if the /VALIDATE option was specified.

dtexec (SSIS Tool): Syntax Rules

• All options must start with a slash (/) or a minus sign (-). The options that are shown here start with a slash (/), but the minus sign (-) can be substituted.
• An argument must be enclosed in quotation marks if it contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space.
• Doubled quotation marks within quoted strings represent escaped single quotation marks.
• Options and arguments are not case-sensitive, except for passwords.

dtexec (SSIS Tool): Exit codes Returned

      When a package runs, dtexec can return an exit code. The exit code is used to populate the ERROR LEVEL variable, the value of which can then be tested in conditional statements or branching logic within a batch file. The following table lists the values that the dtexec utility can set when exiting.

Value Description

  0     The package executed successfully.

  1     The package failed.

  3     The package was canceled by the user.

  4     The utility was unable to locate the requested package. The package could not be found.

  5     The utility was unable to load the requested package. The package could not be loaded.

  6     The utility encountered an internal error of syntactic or semantic errors in the command line.

Explanation

To begin open a Command Prompt and navigate to the Tutorial-Sample-1 project folder as shown below:

It is not necessary to run DTEXEC from the folder where the SSIS package is located; it's just easier to change to the directory for demonstration purposes. 

Type the following command to execute the CreateSalesForecastInput.dtsx package:

DTEXEC /FILE CreateSalesForecastInput.dtsx

To see the complete list of command line options for DTEXEC type:

DTEXEC /?