Blogs

SSIS CI/CD — Azure SQL Virtual Machine

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 

[mailpoet_form id="1"]

Other Recent Blogs

Level 9, 360 Collins Street, 
Melbourne VIC 3000

Level 2, 24 Campbell St,
Sydney NSW 2000

200 Adelaide St,
Brisbane QLD 4000

191 St Georges Terrace
Perth WA 6000

Level 10, 41 Shortland Street
Auckland

Part of

Arinco trades as Arinco (VIC) Pty Ltd and Arinco (NSW) Pty Ltd. © 2023 All Rights Reserved Arinco™ | Privacy Policy
Arinco acknowledges the Traditional Owners of the land on which our offices are situated, and pay our respects to their Elders past, present and emerging.

Get started on the right path to cloud success today. Our Crew are standing by to answer your questions and get you up and running.