Three ways to build SSIS projects in VSTS

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 NuGet1, 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 SSISBuild2, 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 compilation3:

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].xml4
  • 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):

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:

 

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleSlides of CI for BI session