Functions/PublishSsisIspac.ps1

Function Publish-SsisIspac {
    <#
.Synopsis
Publish an ispac to a project folder.
.Description
Publish an ispac and check that it was deployed.
$ispacToDeploy is a file path.
We convert into bits and pass in.
Non-mandatory params here can be used to overwrite the values stored in the publish json file passed in
.Parameter jsonPsCustomObject
Tested json object loaded from Import-Json
.Parameter sqlConnection
The SQL Connection to SSISDB
.Parameter ispacToDeploy
File path to ispac we want to deploy. Convert to byte object then execute as VARBINARY
.Parameter ssisFolderName
Optional parameter. We may wish to override the value of what is in the json file.
.Parameter ssisProjectName
Optional parameter. We may wish to override the value of what is in the json file.
.Example
1)
Publish-SsisIspac -ssisPublishFilePath $thisSsisPublishFilePath -sqlConnection $ssisdb -ispacToDeploy $thisIspacToDeploy
2)
Publish-SsisIspac -ssisPublishFilePath $thisSsisPublishFilePath -sqlConnection $ssisdb -ispacToDeploy $thisIspacToDeploy -ssisFolderName "newFolder"
#>

    [CmdletBinding()]
    param(
        [Parameter(Position = 0, mandatory = $true)]
        [PSCustomObject] $jsonPsCustomObject,
        [Parameter(Position = 1, mandatory = $true)]
        [System.Data.SqlClient.SqlConnection] $sqlConnection,
        [Parameter(Position = 2, mandatory = $true)]
        [string] $ispacToDeploy,
        [Parameter(Position = 3, mandatory = $false)]
        [String] $ssisFolderName,
        [Parameter(Position = 4, mandatory = $false)]
        [string] $ssisProjectName)
    $ssisJson = $jsonPsCustomObject
    $ssisProperties = New-IscProperties -jsonObject $ssisJson
    if ($ssisFolderName) {
        $ssisProperties = Set-IscProperty -iscProperties $ssisProperties -newSsisFolderName $ssisFolderName
    }
    if ($ssisProjectName) {
        $ssisProperties = Set-IscProperty -iscProperties $ssisProperties -newSsisProjectName $ssisProjectName
    }
    [byte[]] $ispacAsBytes = [System.IO.File]::ReadAllBytes($ispacToDeploy)

    $sqlPublishSsisIspac = "
DECLARE @ispac_file VARBINARY(MAX) = @0
DECLARE @operation_id BIGINT
DECLARE @project_binary VARBINARY(MAX)
SET @project_binary = @ispac_file
EXEC CATALOG.deploy_project @folder_name = @1
,@project_name = @2
,@project_stream = @project_binary
,@operation_id = @operation_id OUTPUT
"

    try {
        $msg = "Publishing project " + $ssisProperties.ssisProjectName
        Write-Verbose $msg -Verbose
        $sqlCmdPubIspac = New-Object System.Data.SqlClient.SqlCommand($sqlPublishSsisIspac, $sqlConnection)
        $sqlCmdPubIspac.Parameters.AddWithValue("@0", $ispacAsBytes) | Out-Null
        $sqlCmdPubIspac.Parameters.AddWithValue("@1", $ssisProperties.ssisFolderName) | Out-Null
        $sqlCmdPubIspac.Parameters.AddWithValue("@2", $ssisProperties.ssisProjectName) | Out-Null
        $ispacOperationId = $sqlCmdPubIspac.ExecuteNonQuery()
        $msg = "SQL Script Succeeded. Checking project exists..."
        Write-Verbose $msg -Verbose
    }
    catch {
        $msg = "Publishing " + $ssisProperties.ssisProjectName + " ispac failed."
        Write-Verbose $msg -Verbose
        $pattern = "(?<=operation_messages view for the operation identifier ').+?[0-9]"
        $ispacOperationId = [regex]::match($_.Exception, $pattern).Value
        Write-Error $_.Exception
    }
    finally {
        if ($ispacOperationId -gt 0) {
            $sqlGetOperationIdMessage = "
SELECT om.message
FROM CATALOG.operation_messages om
WHERE om.operation_id = @0
"

            $msg = "Project deployment has failed. This is the output from querying operation_messages view using operation_identifier " +$ispacOperationId
            Write-Verbose $msg -Verbose
            $sqlCommandMessage = New-Object System.Data.SqlClient.SqlCommand($sqlGetOperationIdMessage, $sqlConnection)
            $sqlCommandMessage.Parameters.AddWithValue("@0", $ispacOperationId) | Out-Null
            $sqlOperationMessage = [String]$sqlCommandMessage.ExecuteScalar()
            Write-Verbose $sqlOperationMessage -Verbose
        }
    }
    try {
        $sqlCheckSsisIspacExists = "
SELECT 'exists'
FROM CATALOG.projects project
WHERE project.folder_id = (
SELECT folder.folder_id
FROM CATALOG.folders folder
WHERE folder.NAME = @0
)
AND project.NAME = @1
"

        $sqlCmdVerify = New-Object System.Data.SqlClient.SqlCommand($sqlCheckSsisIspacExists, $sqlConnection)
        $sqlCmdVerify.Parameters.AddWithValue("@0", $ssisProperties.ssisFolderName) | Out-Null
        $sqlCmdVerify.Parameters.AddWithValue("@1", $ssisProperties.ssisProjectName) | Out-Null
        $checkSsisIspacExists = [String]$sqlCmdVerify.ExecuteScalar()
        if ($checkSsisIspacExists -eq "exists") {
            $msg = "Project " + $ssisProperties.ssisProjectName + " exists."
            Write-Verbose $msg -Verbose
        }
        else {
            Write-Verbose "Project does not exist." -Verbose
            Throw;
        }
    }
    catch {
        Write-Error $_.Exception
    }
}