Azure DevOps - Build SSIS project (CI)

Case
I have my SSIS project in Git (Azure Repos Git) and I want to build my project in Azure DevOps. How do I add Continuous Integration (CI) for my SSIS project?

DevOps - Build pipeline

Solution
For this example we start with an SSIS project already in Azure Repos Git and build the project automatically when committing changes. Deployment (CD) of SSIS will be handled in a separate post.
Update Dec 6: New Microsoft SSIS Build task

1) New Build Pipeline
Go to Pipelines and then to Builds. In the list of pipelines you will find the + New sign. Click on it and choose New build pipeline. Now you can choose where your code is located. However on the bottom you will find the option "Use the classic editor to create a pipeline without YAML." For this example we will use this option since SSIS is a bit old school. As last step choose the Empty job and give your CI pipeline a useful name.

Creating new CI pipeline in DevOps for SSIS

2) NuGet Tool Installer
Since we will be needing an SSIS building tool that is available as NuGet, we first need to install NuGet itself. Click on the plus icon behind the "Agent job 1" (you need to give that a better name) and search for "NuGet" choose the NuGet Tool Installer and determine which version of NuGet you need. Fot this example I used 5.1.0 but click on the information icon behind version to get a list of of versions.

DevOps - NuGet Tool Installer

3) NuGet SSISBUILD
Now that we have installed the tool NuGet itself we can install a so called NuGet package. The one we will be needing is called SSISBuild. Add a Task to the agent and search for NuGet again. Choose NuGet and change the command to Custom. Then enter the follow command to install SSISBuild to the Build Server: install SSISBuild -Version 2.3.0

DevOps - NuGet install SSISBuild

4) PowerShell to build SSIS project
Now the most important step: the building of the SSIS project. We will be using PowerShell for this Task. Add a PowerShell Task to run PowerShell on Windows(, macOS, or Linux). Then change the script type to InLine and copy & paste the code below. On the first line of code (excl comments) you have to specify the path of your SSIS project starting from the solution folder untill the extension .dtproj. In the code a Predefined variable called Build.SourcesDirectory will be concatenated to this path to specify the project path on the server.

DevOps - PowerShel task to build the SSIS project

# PowerShell code
# The path of the project starting from the solution folder
$ProjectToBuild = "\SSIS\RM\StandardReportGenerator\StandardReportGenerator.dtproj"

# Join the path of the build server with the path of the project
$ProjectFilePath = Join-Path -Path $env:BUILD_SOURCESDIRECTORY -ChildPath $ProjectToBuild

# Check if the file exists on the build server
if (!(Test-Path $ProjectFilePath)) {
    # Not found, throw error
    throw "Project $($ProjectFilePath) not found!"
}
else
{
    # Call ssisbuild.exe with parameters to build this project
    &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.3.0\tools\ssisbuild.exe" $ProjectFilePath -Configuration Development -ProtectionLevel DontSaveSensitive

    # Check whether the build was successful
    if ($LASTEXITCODE -ne 0) {
        # Build failed, throw error
        throw "Build of $($ProjectFilePath) failed.";
    }
}

You can also test this locally on your own computer. Then you have to download and unzip the SSISBuild nuget to your Visual Studio solution folder and add one line of code at the top to fill Predefined variable with the solution path on your local computer.

# PowerShell code
$env:BUILD_SOURCESDIRECTORY = "D:\sources\xxxxxxbi\"

Test locally with PowerShell ISE

The PowerShell is very basic at the moment (KISS), but there are some options to extend the possibilities for this PowerShell. These will be handled in a separate post.

5) Copy Files
Now that we have successfully build the project it is time to copy the result, the .ispac file, to the Artifact Staging Directory. Add a new task called Copy Files. Select *.ispac as content and use the Predefined variable build.ArtifactStagingDirectory as Target Folder.

DevOps - Copy ispac file to stage folder

6) Publish Build Artifacts
The last task is to publish the artifact, the . ispac file, so you can use it in a Release pipeline later on. Add a task called Publish Build Artifacts. By default the same Predefined variable build.ArtifactStagingDirectory is used as path to publish. Rename the artifact so that you can easily find it when building the Release pipeline.

DevOps - Publish Build Artifacts

7) Add Triggers
To trigger this build pipeline automatically when changes are committed to the Master branch we need to add a trigger. Go to the triggers menu and enable continuous integration. Then add a path filter so that this build will only trigger when the specific project changes. Then save the Build pipeline and trigger it by committing a change.

DevOps - Enable trigger to build SSIS project

Conclusion
In this post I showed you how to build an SSIS project in Azure DevOps with the SSISBuild NuGet. It took quite a few steps, but most steps are relative simple and easy to replicate in your own DevOps project.

One downside of this method is that it is a bit more strict when building your SSIS project. It could even happen that you can build your project in Visual Studio successfully and even deploy it to the catalog, but that it will fail building when using the SSISBuild Nuget and throwing you this error: ERROR: An item with the same key has already been added.

ERROR: An item with the same key has already been added

The possible cause of this error is duplicate Connection Manager Guids. This happens when you copy a package (with a package connection manager) in Visual Studio. I solved it when recreated the connection manager manually.
If you have duplicate package connection managers and you convert one of them to a project connection manager then it will hide the duplicate connection manager in the copied package, making it very hard to find.

Next step: Deploy your SSIS project