SSIS and Azure Devops

Here at Tesco, one of the projects I am working on requires me to use SSIS.. For anyone who doesn't know what SSIS is, it stands for SQL Server Integration Services and is the next version of automation after the old DTS packages.

SSIS can be super useful for moving data around, but deploying it can be a pain... especially building. There are normally three methods;

Build using DevEnv

- task: CmdLine@1
    displayName: 'Build SSIS'
    inputs:
        filename: '"C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Devenv.com"'
        arguments: '"$(Build.SourcesDirectory)\$(Solution)" /rebuild $(SSISBuildConfiguration) /project "$(Build.SourcesDirectory)\src\Tesco.HRAM.IntegrationServices\Tesco.HRAM.IntegrationServices.dtproj"'

Building through Devnv can take up to 20-30minutes.. and when moving to an on-prem build agent, it also requires you to have a valid VS license.

Use the Azure DevOps marketplace - this was my initially prefered option, however, they don't have commercial licenses... no good.

Powershell to the rescue!

- Powershell: |
        $dtProjFolderToLoad = "$(Build.SourcesDirectory)\src\Tesco.HRAM.IntegrationServices"
        $dtProjFileToLoad = Join-Path $dtProjFolderToLoad "Tesco.HRAM.IntegrationServices.dtproj"
        [xml]$dtProjXmlDoc = New-Object System.Xml.XmlDocument
        $dtProjXmlDoc.PreserveWhitespace = $true
        $dtProjXmlDoc.Load($dtProjFileToLoad)

        # Create folder with the project name. This will essentially be zipped into an ispac
        $devFolder = Join-Path $dtProjFolderToLoad "bin\$(SSISBuildConfiguration)"
        if (Test-Path $devFolder)
        {
                Remove-Item $devFolder -Recurse
        }
        $ispacFolder = Join-Path $devFolder "Tesco.HRAM"
        Write-Host $ispacFolder
        New-Item -ItemType Directory -Force -Path $ispacFolder

        # Create the project manifest in the ispac folder
        # Exists in node /Project/DeploymentModelSpecificContent/Manifest/SSIS:Project
        $projectManifestXml = $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.OuterXml
        $projectManifestFullPath = Join-Path $ispacFolder "@Project.manifest"
        $projectManifestXml | Out-File $projectManifestFullPath -Encoding ascii -NoNewline

        # Add [Content types].xml, which has a static content
        $contentTypesXml = "<?xml version=`"1.0`" encoding=`"utf-8`"?><Types xmlns=`"http://schemas.openxmlformats.org/package/2006/content-types`"><Default Extension=`"dtsx`" ContentType=`"text/xml`" /><Default Extension=`"conmgr`" ContentType=`"text/xml`" /><Default Extension=`"params`" ContentType=`"text/xml`" /><Default Extension=`"manifest`" ContentType=`"text/xml`" /></Types>"
        $contentTypesFullPath = Join-Path $ispacFolder '[Content_Types].xml'
        $contentTypesXml | Out-File -LiteralPath $contentTypesFullPath -Encoding utf8 -NoNewline

        # Iterate over all SSIS packages (*.dtsx) inside the .dtproj file add them to the ispac folder
        $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.Packages.Package | ForEach-Object { 
                $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name))
                Copy-Item $fileToCopy $ispacFolder 
        }


        # Iterate over all project-level connection managers (*.connmgr), add them to the ispac folder
        $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.ConnectionManagers.ConnectionManager | ForEach-Object { 
                $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name))
                Copy-Item $fileToCopy $ispacFolder 
        }

        # Copy the parameters file to the ispac folder
        $paramsFullPathSource = Join-Path $dtProjFolderToLoad "Project.params"
        Copy-Item $paramsFullPathSource $ispacFolder

        # Archive the ispac folder as a ".ispac" file
        Compress-Archive ($ispacFolder + "\*") ($ispacFolder + ".zip") -Force
        Rename-Item ($ispacFolder + ".zip") ($ispacFolder + ".ispac") -Force
        Remove-Item $ispacFolder -Recurse
    displayName: 'Build SSIS'

The above takes around 1-2 minutes to run, and you end up with the same outcome!

Easy and fast - All result in a deployable ISPAC file.