Run an SSIS package from the command line using the DTExec utility

Integration Services tutorial to run SSIS packages from the command line using the DTExec utility.

The DTEXEC Integration Services tool allows users to run and configure an SSIS package from the command line. Indeed, the Microsoft SQL Server suite includes the DTExec.exe executable. This is a scripting tool for controlling and setting up SSIS packages. It is possible to launch the package execution utility from a command prompt, or from a batch file, i.e. with the .bat extension.

1. Prepare the SSIS package to execute in command line

To begin, open an MS-DOS command prompt through the Windows Start menu. Then click on Command Prompt. Or by typing cmd in the Windows search bar. Or a simple SSIS package found in this folder:

• C:\ssis\Package.dtsx

It is not necessary to run the dtexec.exe utility from the folder where the SSIS package is located. But the exact and absolute path to the DTSX file must be provided. Or the relative path from the folder where the command is located.

2. How to run SSIS packages stored in a folder with cmd ?

For example, type this command to run a package. This example runs the package with the default settings, i.e. it uses the configuration file (with a dtsconfig extension) by default.

DTEXEC /FILE "C:\ssis\Package.dtsx"

The following SSIS errors with 2 different codes may be displayed:

• SSIS Error Code: 0xC001700A
• Source: Package
• Description: The version number in the package is not valid. The version number cannot be greater than current version number.

• SSIS error Code: 0xC0016020
• Source: Package
• Description: Package migration from version 8 to version 6 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.

Import an XML file into a SQL table with SSIS

3. How to fix the SSIS error 0xC001700A for incorrect version ?

This aspect is depending on the configuration of 3 elements: the PC, the SQL Server, and the development environment. It is possible that multiple versions of the DTExec executable and the database engine coexist. The solution to correct the SSIS error code 0xC001700A is to change the SQL Server version used. To correct the error code, check the versions and compatibility levels of the following three items:

1. The DTExec.exe utility.
2. The SQL Server database engine.
3. The compatibility level of the SSIS project.

3.1 Check the DTExec active version

From the command prompt, type DTExec to display the detailed version number of the SSIS utility.

DTExec

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.7001.0 for 32-bit

It is therefore version 11.0.7001.0 of the DTExec utility that is used. That is to say a version corresponding to SQL Server 2012, according to the list of SQL Server versions and updates available on the official website.

3.2 Check the SQL Server installation version

This check is not mandatory, but it is recommended to keep all blocks up to date. To do this, connect to the database with SSMS and run this query to check the exact and detailed version of the SQL Server engine used.

select @@version as [SQL_Version];

The Microsoft SQL Server 2019 (RTM-GDR) 15.0.2095.3 version is installed.

3.3 Check the compatibility level of the SSIS project to execute

Now check or modify the compatibility level of the SSIS package to be executed with DTExec. From Visual Studio 2019 for example, open the project containing the package to be executed.

1. Right-click on the SSIS project in Visual Studio.
2. Display the project Properties.

Then check or change the TargetServerVersion property, which is the target compatibility level of the SSIS project being developed. With backward compatibility but not forward compatibility, it is not possible to run a SQL Server 2019 package with the SQL Server 2012 version of DTExec. The reverse would be possible.

• From the Project Properties Pages.
• In the Configuration Properties tab and the General section, check and modify the target version to SQL Server 2019, if necessary.

After possibly changing the version, deploy the package again in the source folder before running it.

3.4 Change the PATH environment variable used by the command line

To change the version of the DT Exec software called from MS-DOS, display the PATH variable from a command line.

path

Here are the paths found in the PATH environment variable, directly from the MS-DOS Windows, and displayed online in the list using Notepad++.

• Windows paths with 32-bit MS SQL versions
  • C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\; – SQL Server 2012 (active path)
  • C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\; – SQL Server 2014
  • C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\; – SQL Server 2016
  • C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\; – SQL Server 2017
  • C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\; – SQL Server 2019 (path to use)
  • C:\Program Files (x86)\Microsoft SQL Server\160\DTS\Binn\; – SQL Server 2022
• Windows paths with 64-bit MS SQL versions
  • C:\Program Files\Microsoft SQL Server\150\DTS\Binn\; – SQL Server 2019

3.5 Adapt the DTExec version to match the SSIS project compatibility

Simply move the path to the 64-bit SQL Server 2019 folder before the other paths so that it is selected by default. To do this, follow these steps done with Windows 10:

1. Open Windows settings with the shortcut Windows + I
2. In the search bar, enter Variables
3. From the list of results, select Edit System Environment Variables
4. Then select Environment Variables

To modify the contents of the environment variable, from this window, perform these actions:

• Select the Path variable
• Choose the Edit option

Change the order of the executables in the list. Indeed, for a given executable with the same name, Windows uses the first one in the list, sorted by default in alphabetical order.

• Select the most recent version from the 32-bit folder, i.e. C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;
• Move the value up the list with the Move Up option, until it is the first of the SQL Server related paths.

Reboot the PC to apply the changes made by the operating system.

The DTExec utility is ready now to run using the same compatibility level as the package.

Import an Excel file into a SQL table with SSIS

4. Run the SSIS package with the DTExec command

After restarting the PC, type the DTExec command again from a DOS command prompt to check the new DTExec version. The window now shows 15.0.2000.5.

Finally, run the package again to check that it works properly, with the same command. The package is a very simple one that simply runs a SQL query to display the date using an OLE DB connection to a MS SQL database.

DTEXEC /FILE "C:\ssis\Package.dtsx"

5. Display SSIS command line options using DTEXEC

Finally, it is possible to see the full list of command line options for the dtexec utility directly in MS-DOS. Display the DTEXEC command help by typing this script from a command line prompt.

DTEXEC /?

Conclusion on running SSIS packages with DTExec

Finally, for more details, the official documentation of the dtexec utility is available. It is possible to manage other aspects of the Microsoft BI suite with scripts. Like deploying SSAS cubes with XMLA code or deploying SSRS reports with MS-DOS.

Here is how to deploy an SSIS package from the command line using the MS-DOS prompt.

Deploy SSIS packages with cmd and the dtutil command line tool