Deploy SSIS packages with cmd and the dtutil tool

How to deploy SSIS packages with the cmd command line and the dtutil tool?

To deploy SSIS packages with cmd, SQL Server includes the dtutil executable utility that works with cmd command lines, to deploy dtsx files directly with ms-dos. Dtutil.exe is a good choice for scripting and automating the deployment of SSIS projects. It can be run from a command prompt or from a batch file (.bat extension).

Table of Contents

1. Methods to deploy SSIS packages

What are the different methods for deploying an Integration Services package? The following examples show how to deploy the Package-Budget.dtsx package to the Windows file system, the SSIS package store and MS SQL Server. To deploy SSIS packages to or from Windows, you can use one of three alternatives, a simple copy.

The MS-DOS copy command
The PowerShell Copy-Item command
or directly with the Windows Explorer

2. Copy SSIS packages with DTUTIL

In this tutorial, the dtutil command method, on a single line, is used. Replace the C:\temp path with the target project folder. To begin, open a cmd command prompt and access a project, for example, in the folder containing the packages to deploy.

DTUTIL /FILE Package-Budget.dtsx /COPY FILE;C:\temp\Package-Budget.dtsx

3. Deploy dtsx files to the SSIS package store with cmd

To deploy an SSIS file into the package store, type the following command on a single line.

DTUTIL /FILE Package-Budget.dtsx /COPY DTS;Package-Budget.dtsx

4. Dtutil example to deploy a package to SQL Server

To deploy to SQL Server directly, type the following command, always on a single line.

4.1 Deploy to the default MSSQLSERVER instance

The command below deploys the example package on the default SQL Server instance and on the local machine. Indeed, no machine name is specified as a parameter.

DTUTIL /FILE Package-Budget.dtsx /COPY SQL;Package-Budget

4.2 Deploy to a specific or named instance

To deploy to another SQL Server, or another locally named SQL Server instance, add a parameter to the command line. And specify the destination server, in this form /DESTSERVER “SERVER INSTANCE-NAME”.

DTUTIL /FILE Package-Budget.dtsx /DestServer "SERVER\INSTANCE-NAME" /COPY SQL;Package-Budget

5. Use Azure DevOps to automate deployment

On the other hand, it is easy to schedule operations to load CSV files with SSIS using the SQL Server Agent for example. It is important to automate the deployment of files and new projects releases as much as possible, to avoid errors and regressions. Today, with Azure DevOps, this automation is becoming more and more widespread.

Indeed, it is possible to use simple methods to deploy an SSIS package from a simple copy in a cmd or PowerShell file. But also, with manual deployments from dtutil or Visual Studio which also tend to disappear. In favour of continuous development and integration methods such as Azure DevOps.