Execute SSIS Package from Catalog with PowerShell - Part 2

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


In this second part of the tip, we'll continue the explanation on how you can execute Integration Services packages from a PowerShell script. We'll cover how you can use an environment from the SSIS catalog and how you can use the T-SQL stored procedures from the SSIS catalog to execute a package.


The basics of executing SSIS packages from PowerShell have been laid out in this link. It's recommended you read part 1 first before you continue with the rest of the tip.

Execute On-premises SSIS Package from PowerShell with an Environment

Another method to set the variable during execution time is to use an environment of the SSIS catalog. In such environment, you can store different environment variables (not to be confused with the operating system variables of the same name). These variables can then be linked to the parameters of your project. At runtime, you can choose which environment you use to run the package, dynamically changing its behavior. You can find more info about this set-up in the tip Setup Environment Variables in SQL Server Integration Services.

With the following T-SQL script, an environment is created, along with an environment variable. It is then linked to the SSIS project and the parameter we use in our sample package is set to reference the environment variable.

EXEC [SSISDB].[catalog].[create_environment]@environment_name=N'TEST', @environment_description=N'', @folder_name=N'MSSQLTIPS';
DECLARE @var SQL_VARIANT = N'Hello MSSQLTips from Environment!';
EXEC [SSISDB].[catalog].[create_environment_variable]@variable_name=N'EnvVariable', @sensitive=False, @description=N'', @environment_name=N'TEST', @folder_name=N'MSSQLTIPS', @value=@var, @data_type=N'String';
DECLARE @reference_id BIGINT;
EXEC [SSISDB].[catalog].[create_environment_reference]@environment_name=N'TEST', @environment_folder_name=N'MSSQLTIPS', @reference_id=@reference_id OUTPUT, @project_name=N'MSSQLTIPS', @folder_name=N'MSSQLTIPS', @reference_type=A;
SELECT @reference_id;
EXEC [SSISDB].[catalog].[set_object_parameter_value]@object_type=30, @parameter_name=N'Parameter', @object_name=N'ExecuteFromPowerShell.dtsx', @folder_name=N'MSSQLTIPS', @project_name=N'MSSQLTIPS', @value_type=R, @parameter_value=N'EnvVariable';

To use the environment during the package execution, we need to make a couple of small changes to the PowerShell script. First of all, we need a variable holding the name of the environment.

set environment name variable

Next, we need to load the environments and then find the environment reference to the project. When this is done, we can execute the package by supplying this environment reference.

# Get the environment
$environment = $folder.Environments[$EnvironmentName]
# Get the environment reference
$environmentReference = $project.References.Item($EnvironmentName, $TargetFolderName)            
Write-Host "Running " $PackageName " with environment..."
$result = $package.Execute("false", $environmentReference) #overloaded Execute
Write-Host "Done. " $result

Using an environment leads to shorter and more efficient code, since you can set many parameters at once using one single environment. When executing the package, we can see the environment variable has been used:

success with environment variable

Execute On-premises SSIS Package from PowerShell using Stored Procedures

SSIS offers excellent PowerShell support (and .NET in general), the SSIS catalog also comes with an extensive T-SQL support in the form of stored procedures. If you already use stored procedures to manage your SSIS catalog, it might be an option to use these instead of doing all the work in PowerShell.

Executing an SSIS package with a stored procedure requires multiple steps:

  • First an execution has to be created.
  • If you're going to use an environment, the reference ID needs to be found.
  • Optionally, you can set execution values for (system) parameters.
  • Finally, you start the execution.

To minimize the PowerShell code, we can wrap all of this T-SQL code into one single stored procedure:

CREATE PROC [dbo].[RunPackage]
   (@PackageName       VARCHAR(150)
    ,@FolderName       VARCHAR(150)
    ,@ProjectName      VARCHAR(150)
    ,@EnvironmentName  VARCHAR(150)
   ,@Synchronized      BIT = 1 -- run synchronously by default
DECLARE  @execution_id  BIGINT
        ,@ref_id        INT;
-- find the environment reference ID
SELECT @ref_id = er.[reference_id]
FROM SSISDB.[catalog].[environment_references]  er
JOIN SSISDB.[catalog].[projects]                p ON er.[project_id]    = p.[project_id]
JOIN SSISDB.[catalog].[folders]                 f ON p.[folder_id]      = f.[folder_id]
WHERE   er.[environment_name]   = @EnvironmentName
    AND p.[name]                = @ProjectName
    AND f.[name]                = @FolderName;
EXEC [SSISDB].[catalog].[create_execution]
    @package_name     = @PackageName
   ,@execution_id     = @execution_id OUTPUT
   ,@folder_name      = @FolderName
   ,@project_name     = @ProjectName
   ,@use32bitruntime  = False
   ,@reference_id     = @ref_id;
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
   ,@object_type     = 50
   ,@parameter_name  = N'SYNCHRONIZED'
   ,@parameter_value = @Synchronized;
EXEC [SSISDB].[catalog].[start_execution]@execution_id;
RETURN @execution_id;

The stored proc accepts 5 parameters; the first three pinpoint a specific package (inside a project, inside a folder), the 4th specifies the environment (once a parameter is linked to an environment, it's mandatory to select an environment for execution) and the last one can set the package execution to either synchronous or asynchronous. The stored proc returns the executionID, so we can use it to find the actual result of the package execution, see part 1 for more information.

In PowerShell, we now need to execute this stored proc. Let's do this with a function:

Function Execute-Procedure {
        , [Parameter(Mandatory=$true)][string]$foldername
        , [Parameter(Mandatory=$true)][string]$projectname
        , [Parameter(Mandatory=$true)][string]$environmentname
        , [Parameter(Mandatory=$false)][bool]$synchronized = 1
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=true"
        $query = "dbo.RunPackage"
        $cmd = New-Object System.Data.SqlClient.SqlCommand("$query",$scon)
        $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $cmd.Parameters.AddWithValue("@PackageName", $packagename) >> $null
        $cmd.Parameters.AddWithValue("@FolderName", $foldername) >> $null
        $cmd.Parameters.AddWithValue("@ProjectName", $projectname) >> $null
        $cmd.Parameters.AddWithValue("@EnvironmentName", $environmentname) >> $null
        $cmd.Parameters.AddWithValue("@Synchronized", $synchronized) >> $null
        $cmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
        $cmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue" 
        $cmd.CommandTimeout = 0
        #$cmd.CommandText = "EXEC dbo.RunPackage '$packagename', '$foldername', '$projectname', '$synchronized'"
            $cmd.ExecuteNonQuery() | Out-Null
        catch [Exception]
            Write-Warning $_.Exception.Message

This function accepts the same 5 parameters as the stored proc. A connection is created to the database and a SQLCommand object is created. The actual query is the name of the stored procedure. To make this work, the command type needs to be set to StoredProcedure.

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

Next, the PowerShell parameters are bound to the stored procedure parameters. For example, the package name parameter:

$cmd.Parameters.AddWithValue("@PackageName", $packagename) >> $null

The “>> $null” at the end of the statement tells PowerShell the parameter does not need to be included in the output of the function.

A new parameter is created, which will hold the return value of the parameter. In this case, we need to set the “direction” of the parameter to “ReturnValue”.

$cmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")
$cmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"

Finally, the stored procedure is executed and the value of the ReturnValue parameter is returned. With the following piece of PowerShell, we can call the function and thus execute the stored proc, which on its turn will execute the SSIS package.

# Variables
$TargetFolderName = "MSSQLTIPS"
$ProjectName = "MSSQLTIPS"
$PackageName = "ExecuteFromPowerShell.dtsx"
$EnvironmentName = "TEST"
$out = Execute-Procedure -packagename $packagename -foldername $TargetFolderName -projectname $ProjectName -environmentname $EnvironmentName -synchronized 1
Write-Host $out
$result = $out[1]
Write-Host $result

The result:

result in powershell with t-sql

And in the SSIS catalog:

result in SSIS catalog

The $out object lists the name of the return value parameter and it's actual value. If we hadn't excluded all the other parameters, they would be listed in this array as well. We can get the execution ID of the package by selecting the second item of the array (which is 0-based):

$result = $out[1]
Next Steps

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

Saturday, August 6, 2022 - 4:53:19 PM - Koen Verbeeck Back To Top (90356)
Hi Timusius,

all the PowerShell code worked when I wrote it a couple of years back.
I'm not a PS expert, so it's possible some syntax is not 100% correct, but apparently still good enough to be executed.
In this SO question I see the same syntax for referring to an environment reference: https://stackoverflow.com/questions/49076852/ssis-project-deployment-via-powershell-project-parameters-configured-to-use-en


Friday, August 5, 2022 - 5:10:30 AM - Timusius Back To Top (90347)
Does the example really hold working code? (I cannot get it to pass the environment....)

$environment = $folder.Environments[$EnvironmentName] <--- This variable is never used.

# Get the environment reference
$environmentReference = $project.References.Item($EnvironmentName, $TargetFolderName) <-- Shouldn't this be brackets instead of parenthesis?

get free sql tips
agree to terms