Execute SSIS Package from PowerShell Part 1


By:   |   Updated: 2020-10-09   |   Comments   |   Related: More > PowerShell


Problem

We have a couple of workflows scripted out in PowerShell and in one of those workflows, we would like to execute an SSIS package that was created by another team. Is this possible? And if so, can we pass parameters to the package?

Solution

PowerShell is a powerful cross-platform scripting language, which is popular by admins to automate tasks and workflows. In this part of the tip, we'll take a look on how we can use a PowerShell script to execute an Integration Services (SSIS) package, stored in the SSIS catalog on a local machine (thus using the project deployment model).

Execute SSIS from PowerShell

Let's create a simple package (using the project deployment model) which we will then execute from PowerShell. The package will have one Execute SQL Task, which will write a message to a table in a database. Through a package parameter, we can change this message. In an SSIS project, create a new package and add an Execute SQL Task to the canvas. Also create a connection to your SQL Server database of choice. In this database, create the following table:

CREATE TABLE dbo.PowerShellTest (ID INT IDENTITY(1,1) NOT NULL, TestMessage VARCHAR(100) NOT NULL);

Then create a package parameter of the string data type:

package parameter

As default message, we use "Hello World!". Next, create a variable, again of the string data type. This variable will contain the T-SQL statement that the Execute SQL Task will execute. Using an expression, we will insert the value of the parameter into the SQL statement.

variable with expression

The expression is as follows:

"INSERT INTO dbo.[PowerShellTest]([TestMessage])
VALUES ('"+ @[$Package::Parameter] + "');"

Configure the Execute SQL Task to use this variable as input for the SQL statement:

configure sql task to use variable

When we debug the package, we can see the result in the table:

value inserted into table

Execute Package by Connecting to the SSIS Catalog

Before we do anything, we need to make sure we have the SQL Server cmdlets installed on your machine, otherwise you might get this error:

sqlserver module not found

With the following command we can install this module:

Install-Module SqlServer

If you're asked to install from an untrusted repository (PowerShell Gallery), answer with Yes (Y) or Yes to All (A).

install SQL Server module

Let's take a look at how we can execute an SSIS package with a simple command. First, we need to invoke SQLCMD:

Invoke-Sqlcmd  | Out-Null

Then we set the location to SQL Server:

Set-Location SQLSERVER:\SQL

Then we can retrieve the list of packages using Get-ChildItem. This list will be filtered using the package name and the resulting package will be executed using the Execute method. The entire PS command:

(Get-ChildItem SQLSERVER:\SSIS\localhost\Default\Catalogs\SSISDB\Folders\MSSQLTIPS\Projects\MSSQLTIPS\Packages\ | WHERE { $_.Name -eq 'ExecuteFromPowerShell.dtsx' }).Execute("false", $null)

For a visual reference, here's how the package is stored inside the catalog:

ssis catalog with folder and project

The Execute method accepts at least two parameters:

  • The first one is use32RuntimeOn64, which determines if the 32-bit runtime should be used or not.
  • The second one is reference, which is a reference to an environment created in the catalog. If you don't want to specify an environment, just pass NULL.

The optional third and fourth parameters allow you to specify values for the package parameters or for property overrides respectively.

The Execute method will return a number, which is the execution ID of the package. This means it doesn't return if the package failed or not! In the following screenshot, the package is executed twice. The first time it failed (because of a connection time-out, which can be common with an Azure SQL Database in the serverless compute tier), the second time it succeeded.

executing ssis package with single command

When looking at the All Executions report in the SSIS catalog for the package, we can see both execution IDs:

all executions report

In the table, we can see a new row has been added:

result of second execution

The command is useful for an ad-hoc execution of a single package, but in more realistic use cases more control is probably needed. Let's expand into a full PowerShell script. First, we're going to use some variables to store all the info about the folder, the project and the package. We're also storing the name of the SSIS namespace.

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetServerName = "localhost"
$TargetFolderName = "MSSQLTIPS"
$ProjectName = "MSSQLTIPS"
$PackageName = "ExecuteFromPowerShell.dtsx"

Next, we're loading the SSIS assembly, creating a connection to the SQL Server database where the catalog is stored and we're creating a new SSIS object.

# 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=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
 
# Create SSIS object
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection

Now we can load the catalog, the folder, the project and the desired package into PowerShell.

# 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]

Finally, we can execute the package using the same execute method we used before:

$result = $package.Execute("false", $null) 

Again, the $result variable will hold the executionID of the package and is not an indicator for success. Using the PowerShell ISE, we can execute the script:

execute ssis package through script

Passing Parameters to the Package

Let's find out how we can alter the behavior of a package by passing input parameters. To do this, we're going to set the parameter value using the Set method of the ParameterInfo class. After setting the parameter, the package is altered.

The last piece of the script now becomes:

# Get package
$package = $project.Packages[$PackageName]
 
$paramname = "Parameter"
$paramvalue = "Hello MSSQLTips!"
 
$package.Parameters[$paramname].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$paramvalue);
$package.Alter()
 
Write-Host "Running " $PackageName " with param..."
 
$result = $package.Execute("false", $null)
 
Write-Host "Done. " $result
 

When the script is executed, you can see the parameter value has changed:

result after overwriting design time value

Because we used the Alter method of the package, the design time value of the parameter is overwritten in the SSIS catalog:

configure ssis package

This change is permanent on the server. This is OK if you want to use the parameter value every time the package is run inside the catalog, but not if it was intended for one execution only. If we want to set the parameter value for a single execution, the script becomes a bit more complicated.

We have to use the ExecutionValueParameterSet class. This object will contain the name, the value and the object type. The object type determines what kind of property we want to set. The documentation of the .NET object model is not very clear, but luckily the documentation of the T-SQL counterpart is. The value 20 is for project parameters, the value 30 for package parameters. For setting certain system parameters, such as SYNCHRONIZED, you have to use the value 50.

For each parameter we want to change, we need to create an ExecutionValueParameterSet object and put those into a collection. When we use the Execute method, we will pass this collection as input. The script becomes:

# Get package
$package = $project.Packages[$PackageName]
 
$paramname = "Parameter"
$paramvalue = "Hello MSSQLTips Ad-hoc!"
 
$setValueParameters = New-Object 'System.Collections.ObjectModel.Collection[Microsoft.SqlServer.Management.IntegrationServices.PackageInfo+ExecutionValueParameterSet]';
 
$executionParameter = New-Object 'Microsoft.SqlServer.Management.IntegrationServices.PackageInfo+ExecutionValueParameterSet';
$executionParameter.ObjectType = 30; #package parameter
$executionParameter.ParameterName = $paramname;
$executionParameter.ParameterValue = $paramvalue;
$setValueParameters.Add($executionParameter); #add parameter set to collection
 
Write-Host "Running " $PackageName " with param..."
 
$result = $package.Execute("false", $null, $setValueParameters) #overloaded Execute
 
Write-Host "Done. " $result

When the script is executed, we can verify the parameter value has changed:

execution results

At the same time, the server value of the parameter is still "Hello MSSQLTips!".

Output Parameters

SSIS packages don't have a mechanism for output parameters. If you want your SSIS package to return something, an option is to write it to a table or a file, like we did in the sample package. In PowerShell, you can connect to SQL Server and read data from a table. An example is given in the tip Getting the Count of Non-Null Values in a SQL Server Table with PowerShell.

Package Success or Failure

As demonstrated before, PowerShell only returns the execution ID of the package execution. The Execute method doesn't return if the package succeeded or not. However, with the execution ID we can find the result in the SSISDB catalog view catalog.executions.

results in catalog view

Status code 4 means failure, while status code 7 means success. You can find the other possible codes in the documentation.

Next Steps
  • Try it out yourself! You can find two scripts in this zip file. The first script is where we set the server value of the parameter, the second script uses an execution value for the parameter.
  • As an exercise for the reader, you can try to write a few lines of PowerShell that will fetch the execution result from the catalog view.
  • You can find more PowerShell tips in this overview.
  • Getting started with SSIS? Check out this tutorial.


Last Updated: 2020-10-09


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





download





Recommended Reading

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

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms