Hadi Fadlallah
showing the embedded help of the dtexec utility

An overview of the DTExec utility in SSIS

April 2, 2020 by

DTExec is a tool used to configure and execute SQL Server Integration Services (SSIS) packages. Many developers and database administrators use this tool, especially when automating the package execution using third-party applications or non-supported programming languages.

This article will introduce this tool to you, also help you understand how and when should we use it?

What is a DTExec utility?

DTExec tool is a command prompt tool developed by Microsoft used to configure and execute SSIS packages without needing the Business Intelligence Development Studio or SQL Server data tools. It can run SSIS packages from a file system (*.dtsx), a project file (*.ispac), the msdb database, the Integration Services server, or the SSIS package store.

This tool can access all package configurations such as variables, parameters, connection managers, logging.

When is this tool installed?

For many people, it is not clear what is the SQL Server feature that installs DTExec utility. Koen Verbeeck (a BI consultant) wrote a great article about that and there are two ways to get this tool installed:

  1. Installing SQL Server Management tools (Management Studio): DTExec utility is installed, but it is not available from the command prompt. It is only used by the SQL Server Import and Export Wizard
  2. Installing SQL Server Integration Services client tools: This tool will be installed and available within the command prompt

When is a DTExec utility tool used?

As we explained in our previously published article, Biml alternatives: Building SSIS packages programmatically using ManagedDTS, there are two approaches to access the integration services object model; native or managed.

Integration Services fully supports the Microsoft .NET Framework. This lets the developers access to the fully managed integration service object model to develop, manage, and execute packages. Besides, the managed object model is used by the SSIS designer (BIDS or SSDT), SSIS wizards.

DTExec utility is used when we need to access the object model. Still, we are not using a .NET supported language, or we need to execute packages from the command line, batch file, or windows scheduler.

How can we manually use it?

By default, this tool is installed within the DTS executable files paths: “<installation path>\Microsoft SQL Server\<SQL version>\DTS\Binn” and which is added to the “path” system variable.

The DTS\Binn paths where dtexec utility is located are added bu default to the path system environment variable

Figure 1 – Path system variable values

Getting started

To get started with this tool, just open the command prompt and execute the “dtexec” command, you will get a message telling you that “At least one of the DTS, SQL, ISServer or File options must be specified”.

running dtexec command from command prompt

Figure 2 – Missing options error message

Now, let’s try to list all available options by running the following command:

showing the embedded help of the dtexec utility

Figure 3 – listing all available options

Executing a package from a file system

To execute an SSIS package from a file system, you should use the “/F” or “/FILE” option and specify the package file path. As an example:

Executing a package from a project file

To execute a package from an integration services project (*.ispac) file, you must specify the project file path after the “/Project” option and the package file name after the “/Package” option. As an example:

Execute a package from Integration Services Server

To execute a package from an Integration Services server (SSISDB), you should specify the SQL Server instance after the “/Ser” or “/Server” option and the package path within SSISDB after “/ISServer” option. As an example:

Note that only users with windows authentication can execute SSIS packages from the integration services server.

Execute a package from an SSIS package store

The main difference between the integration services server and the SSIS package store is that packages stored in the SSIS Package Store, are deployed using the legacy package deployment model. In contrast, packages within the integration services server are deployed to the Integration Services server using the project deployment model.

To run a package stored within the SSIS package store, you should use the “/D” or “/Dts” option before passing the file system folder within the package store. As an example:

Executing package from MSDB

To run a package stored within the Msdb database, you should use the “/S” or “/SQL” option before passing the package path within the Msdb database. You should also specify the SQL Server instance by using “/Ser” or “/Server” option. As an example:

Note that if you need to execute the package using SQL authentication, you can use the “/U” or “User” option to specify the user name and “/P” or “/Password” option to write the password. As an example:

Passing parameters

To pass a parameter value (Package or Project), you should use the use “/Par” or “/Parameter” option with the parameter qualified name and value separated by a semicolon (;). You should use this option for each parameter. as following:

Note that this option is only available with the “/ISServer” option.

Passing variables

To pass a variable value, you should use the “/Set” option with the variable qualified name and value separated by a semicolon (;). Also, you should use this option for each variable. As an example:

Note that we can also use this option to set parameter value.

Editing Connections

You can set a connection manager’s connection string property using the “/Connection” option with connection manager name or GUID and the connectionstring value. As an example:

Set Logging level

You can add package logging but use “/L” or “/Logging” option with the log provider and connection. As example:

Set Reporting level

You can set the package reporting level using “/Reporting” option. You should select the reporting options from the list below:

  • N (No reporting)
  • E (Errors are reported)
  • W (Warnings are reported)
  • I (Informational messages are reported)
  • C (Custom events are reported)
  • D (Data Flow task events are reported)
  • P (Progress is reported)
  • V (Verbose reporting)

By default, the reporting level is set to E, W, and P.

Package exit code

When DTExec is executed, it returns an exit code that reflects the error level. The following exit code list was taken from the official documentation:

  • 0: The package ran 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

Validating packages

DTExec utility is not used only to execute packages; it serves as a validation tool. TO validate a package, you can use the “/Validate” option.

Is there any related tool?

There is a related tool called DtExecUI, which is a user interface for this utility. This utility is used when you try to execute a package from SQL Server or when you open it from the command line using “dtexecui” command.

32-bit vs. 64-bit

You can execute SSIS packages in 32-bit or 64-bit mode. As we mentioned above, this utility is installed within two paths Program Files and Program Files (x86). Based on that, if you need to execute the SSIS package in 32-bit mode, you should use the executable in the Program Files (x86) path. For 64-bit mode, you should use the other path.

Conclusion

In this article, we explained what DTExec utility is? When is it installed? And we illustrated some of the different options related to this tool by providing examples.

Hadi Fadlallah
1,599 Views