Execute SSIS Package in Cloud with PowerShell

Problem

In this third part of the tip, we'll continue the explanation on how you can execute Integration Services packages from a PowerShell script. We'll move our packages to the cloud and also cover executing a package that is using the package deployment model.

Solution

The basics of executing SSIS packages from PowerShell have been laid out in Part 1 and Part 2. It's recommended you read both parts first before you continue with the rest of the tip.

Execute an SSIS Package stored in Azure from PowerShell

Using the lift-and-shift capabilities of Azure Data Factory, you can migrate your existing SSIS projects to Azure Data Factory. The packages are stored in an SSIS catalog, which is stored in the SSISDB database. This database is either hosted in an Azure SQL DB, or in an Azure SQL Server Managed Instance. The SQL DB option is the most lightweight and generally more cost-effective. The packages are executed by the Azure-SSIS Integration Runtime (IR), which is hosted in Azure Data Factory (ADF). Scheduling can also be done in ADF. For more information, check out the tip Configure an Azure SQL Server Integration Services Integration Runtime.

When your packages are stored in a cloud SSISDB and executed with the Azure-SSIS IR, we can still start an execution from PowerShell. The script is almost exactly the same as executing an SSIS package stored on-premises. The major difference is you cannot use Windows Authentication to connect to the Azure SQL DB, according to the documentation. This means we need to supply a username and password. The first part of the script becomes:

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetServerName = "mssqltips.database.windows.net"
$TargetFolderName = "MSSQLTips"
$ProjectName = "MSSQLTIPS"
$PackageName = "ExecuteFromPowerShell.dtsx"
$EnvironmentName = "TEST"
$username = "myusername"
$pw = "mypassword"
 
# Load Integration Services assembly
$loadStatus = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Management.IntegrationServices") | Out-Null;
 
# Create connection to the server
$sqlConnectionString = `
    "Data Source=" + $TargetServerName + ";Initial Catalog=SSISDB;User ID=" + $username + ";Password=" + $pw + ";"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

There are definitely better ways to store a username and password in plain text in the PowerShell script, but that's outside the scope of this tip. The tip Microsoft Azure Key Vault for Password Management for SQL Server Applications might be a good start if you're interested in more information about this topic.

The rest of the script remains the same:

# Create SSIS object
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection
 
# Get SSIS catalog
$catalog = $integrationServices.Catalogs["SSISDB"]
 
# Get folder
$folder = $catalog.Folders[$TargetFolderName]
 
# Get project
$project = $folder.Projects[$ProjectName]
 
# Get package
$package = $project.Packages[$PackageName]
 
# Get the environment
$environment = $folder.Environments[$EnvironmentName]
 
# Get the environment reference
$environmentReference = $project.References.Item($EnvironmentName, $TargetFolderName)            
$environmentReference.Refresh() 
 
Write-Host "Running " $PackageName " in Azure with environment..."
 
$result = $package.Execute("false", $null) #overloaded Execute
 
Write-Host "Done. " $result

However, currently there's an issue when you try to invoke the Execute method on a package stored in an Azure SQL DB. The following error message is returned:

Exception calling "Execute" with "2" argument(s): "Could not find stored procedure 'xp_msver'."

It's a known issue when the package is stored in Azure SQL DB. The same script should work when using an Azure SQL Server Managed Instance to store the packages. The issue will be fixed in the future.

Keep in mind the Azure-SSIS IR has to be running to execute an SSIS package in ADF. It's possible to check the status of the IR in PowerShell and start it if necessary. An example can be found in the tip Automate the Azure-SSIS Integration Runtime Start-up and Shutdown - Part 1.

Execute On-premises SSIS Package using the Package Deployment Model

Throughout this tip and the previous two parts, the project deployment model was used to store, configure and execute SSIS packages. Some projects still use the package deployment model though, so let's find out how we can execute such a package with PowerShell.

When using the project deployment model, executing a package outside of the Visual Studio development environment is done using the DTEXEC utility. All we have to do is call this utility from a PowerShell script. In this example, we're using the same package as used in the tip Migrate a Package Deployment Integration Services Project to Azure.

The script is only a few lines long:

$PackageName = "Package.dtsx"
$PackageLocation = "D:\Test\"
$pw = "mssqltips"
dtexec /File ($PackageLocation + $PackageName) /De $pw

The output is printed directly to the screen:

We can check the output in the database to see the package has done its work:

If we want to pass input parameters, we need to use a config file. Keep in mind package/project parameters don't exist in the package deployment model, so with the config file we overwrite the value of a variable. The script now becomes:

$PackageName = "Package.dtsx"
$PackageLocation = "D:\Test\"
$pw = "mssqltips"
$configfile = "D:\Test\myconfig.dtsConfig"
 
dtexec /File ($PackageLocation + $PackageName) /De $pw /Conf $configfile

We can see the expected result in the SQL Server table:

Execute SSIS Package in Azure using the Package Deployment Model

It's also possible to create an Azure-SSIS IR which will execute packages using the package deployment model, as described in the tip Migrate a Package Deployment Integration Services Project to Azure. Executing a package in Azure is done with the new Azure-DTEXEC. The tip Execute SSIS Package in Azure with DTEXEC Utility goes into detail how you can configure and use this utility. The script takes the following form:

$PackageName = "Package.dtsx"
$FileShare = "\\mssqltipsstorage.file.core.windows.net\mssqltips-fileshare\ssis\"
$pw = "mssqltips"
$configfile = "config\myconfig.dtsConfig"
 
$dtexeclocation = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\AzureDTExec.exe"
 
& $dtexeclocation /File ($FileShare + $PackageName) /De $pw /Conf ($FileShare + $configfile)

Again, the Azure-SSIS IR needs to be running before you can execute a package. If not, you get the following error:

Once the IR is started, the package can be executed:

We can verify the output in SQL Server (the config file overwrites the message with "Hello MSSQLTips from Azure!"):

Next Steps

Try it out yourself! You can find all scripts from this tip here. There's one script for executing packages in Azure using the project deployment model (again, which might currently not work due to a bug in Azure) and two for the package deployment model.
If you haven't already, check out Part 1 and Part 2 of this series.
Executing SSIS packages in ADF: Executing Integration Services Packages in the Azure-SSIS Integration Runtime
You can find more PowerShell tips in this overview.
Getting started with SSIS? Check out this tutorial.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-11-05

Execute SSIS Package in Cloud and Package Deployment Model with PowerShell - Part 3