SSIS Package Execution from SSIS Catalog with PowerShell

Problem

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.

Solution

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.

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)            
$environmentReference.Refresh() 
 
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:

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
   )
AS
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]
   @execution_id
   ,@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 4^th 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 {
    Param(
        [Parameter(Mandatory=$true)][string]$packagename
        , [Parameter(Mandatory=$true)][string]$foldername
        , [Parameter(Mandatory=$true)][string]$projectname
        , [Parameter(Mandatory=$true)][string]$environmentname
        , [Parameter(Mandatory=$false)][bool]$synchronized = 1
    )
    Process
    {
        $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'"
 
        try
        {
            $scon.Open()
            $cmd.ExecuteNonQuery() | Out-Null
 
            [int]$cmd.Parameters["@ReturnValue"].Value
        }
        catch [Exception]
        {
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }
    }
}

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:

And in the 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

Try it out yourself! You can find two scripts in this zip file. In the first script an environment is used to set the parameter values of the package. In the second script, T-SQL stored procedures are used to execute the same package.
If you haven't already, check out part 1 of this tip series.
Execute SQL Server Stored Procedures from PowerShell
Execute SSIS Package from Stored Procedure in SQL Server
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-10-27

Execute SSIS Package from Catalog with PowerShell - Part 2