When setting up Continuous Integration in VSTS, chances are you’ll run into the problem of building SSIS projects: where database projects are building just fine, SSIS just doesn’t build out-of-the-box (at least not at the time of writing). Here’s how you fix it:

First things first

First, set your expectations: you won’t create a one-size-fits-all build task that will build all your project types. Instead, you will split up your builds by project type - essentially just as described in Continuous Integration for BI in VSTS: Splitting Build Steps by Project Type.

Building SSIS projects

With folder and solution structure in place, we'll explore three ways to build SSIS projects:

• SSISBuild / SSISDeploy
• Just-for-build SSIS projects
• "Build" inside PowerShell

SSISBuild / SSISDeploy

One way to build your SSIS projects is using SSISBuild. SSISBuild is a program available from NuGet[ref]https://www.nuget.org/packages/SSISBuild/[/ref], which makes it very easy to include at any agent you spin up, either in Azure or self-hosted. It basically provides a native build step doing a SSIS Build step. In order to use SSISBuild[ref]I don't use the accompanying SSISDeploy tooling, because how to do SSIS Deployment from within PowerShell is pretty well documented[/ref], I take the following build steps:

1. Install NuGet
2. Use NuGet to install SSISBuild
3. Call on a PowerShell script to build all .dtproj files

The PowerShell script just loops through my designated SSIS project folders looking for .dtproj files, and calls on SSISBuild to do the compilation[ref]The $($env:XXX) are references to VSTS environment variables, which help me locate where SSIS Build is stored, and what my current Release Configuration is[/ref]:

Get-ChildItem -Filter *.dtproj -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object {
  &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.1.2\tools\ssisbuild.exe" $_.FullName -Configuration $($env:ReleaseConfiguration) -ProtectionLevel DontSaveSensitive
  if ($LASTEXITCODE -ne 0) {
    throw "Build failed.";
  }
}

There are two caveats with this method:

1. SSISBuild hasn’t been updated for over 1.5 year. This hasn’t been a problem (it just works), but it doesn’t feel like a safe bet. Don't worry too much about this: the code is out in the open (Apache license), and it really is pretty clear structured - you can check it out on Roman Tumaykin's GitHub.
2. You need to check-in the “.dtproj.user” file into your VCS - by default, these are ignored by Git and TFS.

Just-for-build SSIS Projects

As an alternative, you can hand-craft a solution which will help you build SSIS projects with MSBuild, like described in VSTS Continuous MSBuild for DWH / BI.

"Build" inside Powershell

A third approach is to create your own build tooling. After all, remember that an .ispac-file is just a zip, containing the following files:

• [Content_types].xml[ref]Really. Whoever did think up of requiring a file with square brackets in the filename?[/ref]
• One or more SSIS packages
• A parameters file
• A project manifest
• Zero or more connection managers

So a very simple initial approach could be like this (Powershell for Windows):

$dtProjFolderToLoad = 'C:\My\Path\To\MySSISProject'
$dtProjFileToLoad = Join-Path $dtProjFolderToLoad 'MySSISProject.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
$ispacFolder = 'C:\My\Path\To\MySSISProject\bin\CustomRelease\MySSISProject'
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 -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 -NoNewline -Encoding "UTF8"

# 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 -Force

This example is pretty basic: for example, it lacks support for password-protected projects, and doesn't check for Windows line-endings. It will suffice for most SSIS projects I'm currently using with my customers though. It's also way easier to maintain for a BI developer than a separately compiled executable.

Wrapping up

I've shown you three ways of building your SSIS projects inside VSTS:

• using external tooling (like SSISBuild)
• creating a custom project file for MSBuild
• creating your own build logic to generate your .ispac-file.

For me, it was quite a revelation to realise I don't have to use the Microsoft libraries to "build" my ispac files, but can easily create them myself - basically because there is no real "building" going on. I'm pretty curious which method you prefer (as long as there's no "out-of-the-box" support for SSIS projects, of course), so by all means send me a message about your experiences. If you run into problems, don't hesitate to contact me as well!

Want to learn more?

If you'd like to learn more, I'll be speaking about this subject at the Microsoft Advanced Analytics Usergroup (Mechelen, Belgium). For a more in-depth look, I've created a course about setting up CI / CD using VSTS in Microsoft BI environments. For more information about the course check:

Azure DevOps voor Data Engineers