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


By:   |   Updated: 2020-11-05   |   Comments   |   Related: 1 | 2 | 3 | More > 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:

powershell output

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

package success without config

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:

package success with config file

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:

azure dtexec error not started

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

azure-dtexec success in powershell

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

success powershell azure-dtexec result
Next Steps


Last Updated: 2020-11-05


get scripts

next tip button



About the author





Comments For This Article





download





Recommended Reading

Execute SSIS Package from PowerShell - Part 1

Execute SSIS Package from Catalog with PowerShell - Part 2

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files














get free sql tips
agree to terms