DeploySSISPackages.ps1

# Helper Funtions
function CatalogFolderDropContent($catalog, $folderName)
{
    if($catalog.Folders[$folderName] -eq $null){
        return;
    }

    while ($catalog.Folders[$folderName].Environments.Count -ne "0")
    {
        $env = $catalog.Folders[$folderName].Environments | Select-Object -First 1
        $catalog.Folders[$folderName].Environments[$env.Name].Drop();
    }
    while ($catalog.Folders[$folderName].Projects.Count -ne "0")
    {
        $prj = $catalog.Folders[$folderName].Projects | Select-Object -First 1
        $catalog.Folders[$folderName].Projects[$prj.Name].Drop();
    }
}

# Variables
$SolutionName = $OctopusParameters["SSISDB.DeployFolder"]
$ProjectFilePath = $OctopusParameters["SSISDB.ProjectFolderPath"]
$SSISDBServerEndpoint = $OctopusParameters["SSIS.DBServerEndpoint"]
$SSISDBServerAdminUserName = $OctopusParameters["SSISDB.ServerAdminUserName"]
$SSISDBServerAdminPassword = $OctopusParameters["SSISDB.ServerAdminPassword"]
$SSISDBAzureUsername = $OctopusParameters["SSISDB.AzureUsername"]
$SSISDBAzurePassword = $OctopusParameters["SSISDB.AzurePassword"]
$SSISDBAzureSubscription = $OctopusParameters["SSISDB.AzureSubscription"]

# Login on Azure
$accountName = $SSISDBAzureUsername
$password = ConvertTo-SecureString $SSISDBAzurePassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($accountName, $password)
Login-AzureRmAccount -Credential $credential

# Set Subscription Context
Set-AzureRmContext -SubscriptionName $SSISDBAzureSubscription

# Load the IntegrationServices Assembly
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.Management.IntegrationServices.dll'
#[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID="+ $SSISDBServerAdminUserName +";Password="+ $SSISDBServerAdminPassword + ";Initial Catalog=SSISDB"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

# Get the catalog
$catalog = $integrationServices.Catalogs['SSISDB']

$projects = Get-ChildItem -Path $ProjectFilePath -File -Filter *.ispac -Recurse
if ($projects.Count -gt 0)
{
    foreach($projectfile in $projects)
    {
        # Deleting existing folder content
        Write-Host "Deleting Folder's content " $SolutionName " ..."
        CatalogFolderDropContent $catalog $SolutionName

        if($catalog.Folders[$SolutionName] -eq $null){
            # Create a new folder
            Write-Host "Creating Folder " $SolutionName " ..."
            $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $SolutionName, "Folder description")
            $folder.Create()
        }
               
        # Read the project file, and deploy it to the folder
        Write-Host "Deploying " $projectfile.Name " project ..."
        [byte[]] $projectFileContent = [System.IO.File]::ReadAllBytes($projectfile.FullName)
        $catalog.Folders[$SolutionName].DeployProject($projectfile.Name, $projectFileContent) 
        
        #Setting parameters, parameters name have to follow this convetion: "ProjectName-Key"
        $projectfilename = $projectfile.Name.Replace(".ispac", "-*");
        $parameters = $OctopusParameters.keys | Where-Object {$_ -like $projectfilename}
        write-host "Parameters found:" $parameters.count
        foreach($parameter in $parameters)
        {
            $key = ($parameter -split '-')[1]
            write-host "Key: $key"
            $value = $OctopusParameters[$key]
            write-host "Value: $value"
            
            if($catalog.Folders[$SolutionName].Projects[$projectfile.Name].Parameters[$key] -eq $null)
            {
                write-host "Parameter $key not found"
            }
            else{
                $catalog.Folders[$SolutionName].Projects[$projectfile.Name].Parameters[$key].Set(       
                [Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,            
                $value) 
            }
        }
        $catalog.Folders[$SolutionName].Projects[$projectfile.Name].Alter()   
    }
}

Write-Host "All done."