Execute SSIS Package in Azure with DTEXEC Utility

By:   |   Updated: 2020-09-15   |   Comments   |   Related: More > Azure

Moving SQL Server workload to the cloud

Free MSSQLTips Webinar: Moving SQL Server workload to the cloud

Attend this session to learn how managing performance is even more critical with Azure SQL DB and Azure Managed Instance. Learn what key performance indicators are most important, what auto-tuning really means and get some tools to help you identify performance issues and correctly size your database.


We have an Integration Services project deployed to Azure Data Factory, where we use an Azure-SSIS Integration Runtime to run the packages. Is it possible to execute packages from a command line on a local machine? We would for example use an on-premises scheduler to schedule the execution.


You can migrate your Integration Services (SSIS) project to the Azure cloud by using an Azure-SSIS Integration Runtime, which is configured and maintained in Azure Data Factory (ADF). Typically, you schedule and execute your SSIS packages using an ADF pipeline, but sometimes there’s the need to start the execution of a package from an on-premises server (to be clear, the actual execution is still in Azure). For example, when you have a hybrid scenario where you combine on-premises and cloud ETL components, but you only want one scheduler.

In an on-premises scenario, you use the utility DTEXEC to execute SSIS packages from the command line (or from any tool able to start an executable). For the cloud, a new version of DTEXEC was created: the Azure-enabled DTEXEC. Although not explicitly mentioned in the documentation, it seems you can only use the Azure-enabled DTEXEC for packages in the package deployment model. You can find more information about this deployment model in Azure in the tip Migrate a Package Deployment Integration Services Project to Azure.

How to use Azure-enabled DTEXEC


The regular DTEXEC utility comes with the installation of SQL Server or the Integration Services service. For the Azure-enabled version, we need one of the later versions of SSMS installed on the machine (version 18.3 or later). Before we can use the DTEXEC, we need to specify how it can connect to the various components in Azure.

In SSMS, go to Tools > Migrate to Azure > Configure Azure-enabled DTExec.

launch config

In the configuration menu, there are several items that need to be provided.

config menu

The first section, Azure AD Authentication, needs information for an Azure AD App that will create pipelines in ADF on your behalf. Let’s create such an app. In the Azure Portal, go to Azure Active Directory. Go to App Registrations and create a new registration.

create new app registration

Give the app a name, choose the supported account types (the default – single tenant – is fine) and specify a redirect URL. You can enter any valid URL.

register the app

To be able to authenticate to the app, a client secret (also known as authentication key) must be created. Go to the Certificates & Secrets section and click on New client secret to create one.

create new client secret

You can specify a description and choose when it expires:

set expiration date

The client secret is created. Be sure to copy the value, as we need it later on.

Next, we need to give this app registration the necessary permissions to create pipelines in ADF. Still in the Azure Portal, navigate to Azure Data Factory and to the access control section. Go the Role assignments and add a new one.

add new role assignment

Assign the app to the contributor role:

assign app to contributor role

You should see the app listed in the list of role assignments:

app added as contributor

We can now provide the necessary values for the Azure-enabled DTEXEC config. You can find the app ID and the tenant ID in the overview page of the app registration we created earlier:

app registration info

For the Azure-SSIS IR, you need to fill in the name of your Azure Data Factory, the name of the Integration Runtime (using Azure Files as the method for storing packages, which is needed for the package deployment model), the resource group, the ID of your subscription (which can be found in the Azure Portal) and the length of the hash which is appended to the name of each automatically created pipeline.

If you want SSIS to log, you can provide a UNC path and the user credentials needed to access this path. In the example, the logs are written to the same Azure Files storage account. We also need to specify the user credentials needed to access the location of the SSIS package. You will also need to specify a logging level, which are the same as the logging levels used in the SSIS catalog (even though we’re not using the project deployment model).

The actual location is provided as an execution parameter when the DTEXEC utility is executed. In this case, the user credentials for the SSIS package location and the log location are the same. The tip Migrate a Package Deployment Integration Services Project to Azure goes into more detail how you can configure the package and log location for the package deployment model.

Executing a Package

Once the utility is configured in SSMS, it can be used to execute packages in the Azure-SSIS IR (make sure it is running). The utility itself can be found in the folder C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn (this might change in the future if SSMS get upgraded).

In the same folder, you can also find the config file which we created in the previous section trough the SSMS user interface. Start up the command prompt, and enter the following command to execute an SSIS package:

AzureDTExec.exe /F \\mystorage\myfolder\myPackage.dtsx /Conf \\mystore\myfolder\myconfig.dtsConfig /De mypassword

Specifying a config file or a password is optional and depends on how you created your package.

executing ssis package in cmd

Once the package has finished, you will be presented with the execution status, the path of the log file, the runtime duration and a bit of extra information.

package has finished succesfully

In the Azure storage account, we can verify the logging has been created:

created log files

In Azure Data Factory, we can see a new pipeline has been created (the name has a hash suffix of 32 characters, as was specified in the configuration):

new pipeline

In the pipeline, an Execute SSIS package activity is used. It would be possible to schedule this newly created pipeline for further use, but this is discouraged as parameters are used and these might change in the future:

newly created pipeline with activity and parameters


In this tip we showed you how you can configure the Azure-enabled DTEXEC and how you can use it to execute an SSIS package in an Azure-SSIS Integration Runtime. This utility can be used by any on-premises scheduling tool for the scheduling of your SSIS packages.

Next Steps

Last Updated: 2020-09-15

get scripts

next tip button

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks

get free sql tips
agree to terms