Execute SSIS remotely - PowerShell

Since SQL Server 2012, Microsoft introduce a new way of interacting and storing SSIS package. SSIS packages are frequently executed in scheduling format often through SQL Server agent jobs. Today blog post will be focusing on remotely calling SSIS package stored in this new SSISDB Catalog.

SSISDB Catalog is the new center point of working with these objects including SSIS Projects, Packages and its parameter and environment. These objects are stored in the SSISDB database. The database is automatically created when Integration Service Catalog is created. Interaction with objects stored in SSISDB Catalog is performed mainly through the SSIS Catalog UI or calling the provided stored procedures in SSISDB.

There are few methods to remotely call the SSIS packages in SSISDB Catalog. One method is through Microsoft.SqlServer.Management.IntegrationServices Namespace, or utilize the SSISDB stored procedures. Please beware that executing SSIS stored in msdb, SSIS package store or file system are different.

The below PowerShell example utilize the SSISDB stored procedures. It first executes the SSISDB catalog.create_execution stored procedure with all the parameter values to create an instance of execution, and subsequently executes the catalog.start_execution stored procedure to start the particular execution instance just created.

function Invoke-SSISPackage {
    [CmdletBinding(DefaultParametersetName="SSPI")] 
    param(
        [parameter(Mandatory=$true, Position=0)]
        [string]$SQLInstance,

        [parameter(ParameterSetName='User', Mandatory=$true, Position=1)]
        [string]$User,

        [parameter(ParameterSetName='User', Mandatory=$true, Position=2)]
        [string]$password,

        [parameter(ParameterSetName='SSPI', Mandatory=$true, Position=3)]
        [switch]$SSPI,

        [parameter(Mandatory=$true, Position=4)]
        [string]$Folder,

        [parameter(Mandatory=$true, Position=5)]
        [string]$Project,

        [parameter(Mandatory=$true, Position=6)]
        [ValidatePattern('^.*\.dtsx$')]
        [string]$Package,

        [parameter(Position=7)]
        [switch]$RunIn32Bit
    )

    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = "Server=$($SQLInstance);`
                                 Database=SSISDB;`
                                 User=$($User);`
                                 Password=$($Password);`
                                 Integrated Security=$( @{$true="SSPI"; $false="False"}[$SSPI -eq $true] )" 
    try {
        $sqlConn.Open()

        Write-Host "Creating SSIS execution.." -ForegroundColor "Yellow"

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand ("[catalog].[create_execution]", $sqlConn)
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.Parameters.AddWithValue("folder_name", $Folder) | Out-Null
        $sqlCmd.Parameters.AddWithValue("project_name", $Project) | Out-Null
        $sqlCmd.Parameters.AddWithValue("package_name", $Package) | Out-Null
        $sqlCmd.Parameters.Add("use32bitruntime", [System.Data.SqlDbType]::Bit).Value = $RunIn32Bit.IsPresent
        $sqlCmd.Parameters.Add("execution_id", [System.Data.SqlDbType]::BigInt).Direction = [System.Data.ParameterDirection]::Output
        $sqlCmd.ExecuteNonQuery() | Out-Null
        
        [int64]$execID = $sqlCmd.Parameters["execution_id"].Value 
        $sqlCmd.Dispose()

        Write-Host ""
        Write-Host "Starting SSIS execution.." -ForegroundColor "Yellow"

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand ("[catalog].[start_execution]", $sqlConn)
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.Parameters.AddWithValue("execution_id", $execID) | Out-Null
        $sqlCmd.ExecuteNonQuery() | Out-Null
        
        $sqlCmd.Dispose()
    }
    catch {
        throw
    }
    finally {
        $sqlConn.Dispose()
    }
}

Although the example above uses PowerShell, implementing it in other programming language such as C#.NET or VB.NET could be easily followed with minor changes. Please note that the script returns result if execution is started successfully, but not if the SSIS completes its execution successfully.

Hope you find the example script useful.