Execute SSIS Package from PowerShell - Part 1

By:   |   Comments (4)   |   Related: 1 | 2 | 3 | > PowerShell


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?


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:


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:


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!"
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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

Comments For This Article

Monday, August 22, 2022 - 11:39:58 AM - Koen Verbeeck Back To Top (90399)
Hi Safiya,

you should be able to execute an SSIS package with PS from a remote machine.
I have no idea about the dll errors. Maybe you can try a powershell forum?


Friday, August 19, 2022 - 12:00:19 AM - Safiya Back To Top (90389)

Thank you for the detailed post, Very helpful. so this PowerShell can execute from a remote machine right? Need not be in SQL Server where the catalog is right? Also I am getting the dll could not be loaded issue. Any idea how to fix? Integration services and Sql.Dmf.common dlls are available both 13 and 14 but the PowerShell couldnt load them. Any help will be appreciated. Thank you

Wednesday, May 26, 2021 - 10:48:13 AM - Koen Verbeeck Back To Top (88746)
Hi Chris,

I believe in PowerShell the boolean values are $true and $false.

Wednesday, May 26, 2021 - 7:02:47 AM - ChrisW Back To Top (88742)
Very nice post! it worked for me except for one thing.
I want to use a parameter with type boolean and get the following message:
"The data type of the input value is not compatible with the data type of the 'Boolean'"
Do you know, where i can define the data type?

get free sql tips
agree to terms