August 31, 2023

SSIS CI/CD — Azure SQL Virtual Machine

Written by Ismael Oltra

These days it is less common to find yourself working with such an old technology like SSIS, as more and more clients have made the move or are moving into the cloud, using Azure Data Factory to orchestrate and execute the ETL/ELT pipelines.

In a recent project, the client was using SSIS with a fully manual deployment into a SQL Virtual machine resource. After some discussions the client decided to explore the benefits of a CI/CD fully automated deployment process.

The clients environment

SSISDB is in a SQL server in an Azure SQL Virtual Machine resource
Azure pipelines agent pool

Deployment yaml pipeline

Yaml script that passes the parameters to a PowerShell script. In this particular implementation, it only accepts one project, if you want to deploy multiple project, you will need multiple tasks.

- task: PowerShell@2
          displayName: 'SSIS: PS1 Script - Deploy SSIS'
          inputs:
            targetType: filePath
            filePath: '.\scripts\SSIS_Deployment.ps1'
            arguments: > 
              -ssisTargetServerName '${{ parameters.ssisTargetServerName }}' 
              -ssisSolutionName 'SolutionName ETL'
              -ssisProjectName 'CRM ETL' 
              -ssisProjectNameSuffix '${{ parameters.ssisProjectNameSuffix }}'
              -ssisProjectRootFilePath '${{ parameters.ssisProjectRootFilePath }}'

PowerShell script, this is the first challenge, where you will most likely have to specify the Integration services assembly that is in the GAC (Global assembly Cache).

param (    
    [parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $ssisTargetServerName,
    [parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $ssisSolutionName,
    [parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $ssisProjectName,
    [parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $ssisProjectNameSuffix,
    [parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $ssisProjectRootFilePath
)

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetFolderName = $ssisSolutionName +  $ssisProjectNameSuffix

Write-Output "This is the root file path--> " $ssisProjectRootFilePath

$ProjectFilePath = $ssisProjectRootFilePath + $ssisSolutionName+"/"+$ssisProjectName+"/bin/Development/"+$ssisProjectName+".ispac"

# Load the IntegrationServices assembly
[System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+    "Version=14.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL")

# Create a connection to the server
$sqlConnectionString = `
    "Data Source=" + $ssisTargetServerName + ";Initial Catalog=SSISDB;Integrated Security = SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

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

# Get the Integration Services catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

$folder = $catalog.Folders[$TargetFolderName]

if (!$folder)
{
# Create the target folder
    $folder = New-Object $SSISNamespace".CatalogFolder" ($catalog, $TargetFolderName,    "Folder description")
    $folder.Create()
}

Write-Host "Deploying " $ssisProjectName " project ..."

# Read the project file and deploy it
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ssisProjectName, $projectFile)

Write-Host "Done."

Challenges and solutions

Challenge:

Azure pipelines agent pool cannot reach the SQL server on Virtual Machine
SSIS deployment cannot be done using a SQL login, instead it needs to use Integrated security

Solution:

Create a local user in the SQL VM ( i.e. IM-SSIS-DEV\srv_DevopsAgent )
Create a login for the local VM user and add the user to the SSISDB as a db_owner and ssis_admin roles

Install a self hosted agent pool in the SQL VM and configure it to run with the local user (srv_DevopsAgent)

.\config.cmd --unattended --url https://myaccount.visualstudio.com --auth pat --token myToken --pool default --agent myAgent --runAsAutoLogon --windowsLogonAccount myDomain\myUserName --windowsLogonPassword myPassword