Azure DevOps - Deploy SSIS project (CD)

Case
Recently you showed how to build an SSIS project in DevOps, but how do you deploy an SSIS project within DevOps.

Release SSIS in DevOps

Solution
There are no out of the box tasks yet, but there are a couple of Third Party DevOps tasks for SSIS which you could try-out. For this example we will use little PowerShell code which we have done before.
Update Dec 6: New Microsoft SSIS Deploy task

1) New Release Pipeline - Empty Job
The first step is to create a new Release Pipeline. For this example we will use an empty job to start with and later on add tasks manually.

• Go to the Pipelines menu on the left
• Go to Releases
• Click on + New to create a New Release pipeline
• Next choose 'Empty job'

Next step is to give the first Stage its name. A Stage is an environment like acceptance or production where you want to deploy to. For this example we will have three stages / environments: Dev/Tst, Acc and Prd. The next two stages will be created by using cloning, but first we will add tasks so that you don't have to repeat yourself.

• After choosing 'Empty job' a first stage is created. Rename it 'DEV/TST'
• Close the Stage pane by clicking the cross in the upper right corner

Create Relesae Pipeline

2) Select Artifact
In the Artifact pane you need to select the Artifact (the ispac file) you created in the Build Pipeline. So look the name before you start.

• In the Artifacts pane click on + Add to add an artifact
• Choose the project if you have multiple DevOps projects
• Select the Artifact of the CI pipeline
• Select Latest version
• Optionally rename the source alias
• Click on the Add button

We also need to determine whether we want to use the Continuous deployment trigger or perhaps use an other trigger or scheduling. For this example we will use the CD trigger.

• Click on the lightning icon
• Enable Build trigger (to release after each new successful build)
• Close the CD trigger pane by clicking the cross in the upper right corner

Add Artifact and enable Continuous deployment

3) Add task to first stage
Now we need to add a task to do the actual deployment of the SSIS ispac file. There are a couple of third party SSIS deployment tasks, but the instructions and examples of those are not always that clear. Hopefully Microsoft will create there own SSIS devops tasks soon. For now we will use a little Powershell scripting.

• Within the first Stage pane, click on the '1 job, 0 task' link to go to the job
• Optionally change the settings of the Agent job (for example its name)
• Click on the + icon in the Agent Job to add a new task
• Search for 'Powershell'
• Add the task called PowerShell (Run a PowerShell script on Linux, macOS, or Windows)
• Click on the new task and rename it to Deploy SSIS project
• Select Inline as Type
• Paste the script below in the Script textbox

Add PowerShell code to deploy SSIS

Notice the Params piece in the PowerShell code. You could hardcode each value, but for this example we will use Variables from the Release pipeline to set the value of these parameters in the next step. The string "$(SsisServer)" in the code will be replaced by the value of the DevOps variable and result in: "bitools.database.windows.net" (see next step).

# PowerShell code
# Params
$SsisServer ="$(SsisServer)"
$SSISDBUsername = "$(SsisDBUsername)"
$SSISDBPassword = "$(SsisDBPassword)"
$FolderName = "RM"

# Mask the password to show something on
# screen, but not the actual password
# This is for testing purposes only.
$SSISDBPasswordMask = $SSISDBPassword -replace '.', '*'
 

Write-Host "========================================================================================================================================================"
Write-Host "==                                                         Used parameters                                                                            =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server            : " $SsisServer
Write-Host "SQL Username           : " $SSISDBUsername
Write-Host "SQL Password           : " $SSISDBPasswordMask
Write-Host "========================================================================================================================================================"

############## ASSEMBLY ###############
# Add SSIS assembly so you can do SSIS stuff in PowerShell
Write-Host "Referencing SSIS assembly"
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;


############## SQL SERVER ###############
Write-Host "Connecting to Azure SQL DB server $($SsisServer)"

# Create a connectionstring for the Azure DB Server
# Make sure you use SSISDB as the Initial Catalog!
$SqlConnectionstring = "Data Source=$($SsisServer);User ID=$($SSISDBUsername);Password=$($SSISDBPassword);Initial Catalog=SSISDB;"
 
# Create a connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
 
# Check if the connection works
Try
{
    $SqlConnection.Open();
    Write-Host "Connected to Azure SQL DB server $($SsisServer)"
}
Catch [System.Data.SqlClient.SqlException]
{
    Throw  [System.Exception] "Failed to connect to Azure SQL DB server $($SsisServer), exception: $($_)"
}


############## SSISDB ############### 
# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
  
# Check if SSISDB connection succeeded
if (-not $IntegrationServices)
{
    Throw  [System.Exception] "Failed to connect to SSISDB on $($SsisServer)"
}
else
{
    Write-Host "Connected to SSISDB on $($SsisServer)"
}

# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]
  
# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages.
    # Also make sure the catalog is SSISDB and not master or any
    # other database.
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}

############## CATALOG FOLDER ###############
# Create object to the (new) folder
$Folder = $Catalog.Folders[$FolderName]
  
# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $FolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $FolderName "found"
}
 
############## LOOP ISPACS ###############
Get-ChildItem -Filter "StandardReportGenerator.ispac" -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object {

    ############## PROJECT ###############
    $IspacFilePath = $_.FullName

    # Check if ispac file exists
    if (-Not (Test-Path $IspacFilePath))
    {
        Throw  [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
    }
    else
    {
        $IspacFileName = split-path $IspacFilePath -leaf
        Write-Host "Ispac file" $IspacFileName "found"
    }

    # Get project name from ispac file
    $ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)

    # Reading ispac file as binary
    [byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath) 
    $Folder.DeployProject($ProjectName, $IspacFile) | Out-Null
    $Project = $Folder.Projects[$ProjectName]
    if (-not $Project)
    {
        # Something went wrong with the deployment
        # Don't continue with the rest of the script
        return ""
    }

    Return "Ready deploying $IspacFileName " 
}

Note: this script uses a DB user to connect to the SSIS DB. For other authorisations you need to change the code block starting on line 30.

4) Add variables
For this example we need three variables: SsisServer, SsisDBUsername and SsisDBPassword

• Go to the Variables tab
• Click on the + Add to add the first variable
• Enter the name of the variable
• Enter the value of the variable
• Hit the padlock icon when it's a sensitive value like a password
• Select the first stage 'Dev/Tst' as scope

Repeat this for all three variables and later on, after the next step, repeat this for each Stage. Just add the same variables but change the scope to Acc or Prd.

Add variables to DevOps

5) Add stages
Now we need to clone the Dev/Tst stage to Acc and to Prd.

• Click on the Pipeline tab if you don't see your first Stage
• Hover above the first stage and wait for the clone button to appear and click it
• Next rename it to Acc
• Repeat this for Prd

Notice the red exclamation mark before the variables tab after adding a stage: you need to add the same variables for each stage.

Adding stages for Acc and Prd

For each Stage you can determine how that Stage starts. These are the 'Pre-deployment conditions'. For example for Dev/Tst you could do an automatic deployment, for Acc you first need an approval from one person and for Prd you need two approvals.

• Click on the lightning/person icon on the left side of each Stage to set it
• Dev/Tst will use the 'After release' trigger and the next stages will use the 'After stage trigger'
• For Acc and Prd you could add Pre-deployment approvals by selecting one of your teammembers.
• Close the Pre-deployment conditions pane by clicking the cross in the upper right corner
• Repeat this for all stages

And don't forget to add the variables when you have finished the Pre-deployment settings.

Adding Pre-deployment conditions for each stage

6) Test
Now create a new release by activating the build task. After this a new artifact will be created and this will trigger the release pipeline. You can also hit the Create Release button in the upper right corner to use the last created artifact. The artifact will automatically be released to the first stage, but the next step waits for approval (click on the blue ACC button to approve).

Dev/Tst succesful, Acc waiting for approval

You can also check the result of the release by clicking on Dev/Tst and then click on the PowerShell step. If it failed you can see the error messages per step.

See result of deployment

Conclusions
In this step you saw how to create a Release pipeline with several Stages and learned how these deployment stages are activated with the Pre-deployment conditions. Unfortunately there are no out-of-the-box Microsoft SSIS devops tasks which force you to either use Third Party tasks or PowerShell. Also take a look around and see all the other options in the build pipeline.