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
