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:
- 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
- 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.
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”.
Figure 2 – Missing options error message
Now, let’s try to list all available options by running the following command:
|
1 |
dtexec /help |
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:
|
1 |
dtexec /File “D:\packagetest.dtsx” |
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:
|
1 |
dtexec /Project “C:\project.ispac” /Package “test.dtsx” |
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:
|
1 |
dtexec /Server “My-Pc/SQLInstance” /ISServer “SSISDB\MyFolder\MyProject\MyPackage.dtsx” |
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:
|
1 |
dtexec /Dts “\File System\PackageTest” |
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:
|
1 |
dtexec /SQL “\Import\ImportPackage.dtsx” /Server “My-Pc\SQLInstance” |
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:
|
1 |
dtexec /SQL “\Import\ImportPackage.dtsx” /Server “My-Pc\SQLInstance” /User sa /Password 123456 |
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:
|
1 |
Dtexec /ISServer "SSISDB\MyFolder\MyProject\MyPackage.dtsx" /server "My-Pc\SQLInstance" /parameter $Project::myparam;myvalue /parameter $Project::myparam2;myvalue2 |
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:
|
1 |
dtexec /File testpackage.dtsx /Set \package.variables[myvariable].Value;myvalue |
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:
|
1 |
dtexec /File F:\testpackage.dtsx /Connection SourceOleDbConnection;” Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;” |
Set Logging level
You can add package logging but use “/L” or “/Logging” option with the log provider and connection. As example:
|
1 |
dtexec /File "F:\testpackage.dtsx" /Logging "DTS.LogProviderTextFile;F:\Packagelog.txt" |
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.
|
1 |
dtexec /File “F:\testpackage.dtsx” /Validate |
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.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023