Automating building of SSIS projects

I am currently working for a healthcare solutions company on one of their leading products. Presently we are working on improving our continuous build/deployment processes. I have been looking into how to include all our SQL Server Integration Services (SSIS) projects and would like to share the approach taken for the continuous build in this blog post.

Automating the build of SSIS project(s) is not as straightforward as it is for the .Net projects. One of the main barriers is that for SSIS projects there is no native support in the visual studio standard build tool (MSBuild).

If you wish to use MSBuild you would need to write your own build file with a custom extension library that you would have either created yourself or downloaded from a community/open source code sharing site.My approach was to use PowerShell to do the following:
  1. Read an XML config file which has the solution and projects files paths along with build configuration.
  2. Find the path to visual studio. By using vswhere.exe looking for version 15 and the installation path.
  3. Then automate visual studio to build require projects
  4. Finally copies all the built output into an artefact folder structure.
I develop this script to run on a development computer or the build server. Integrating this script into our build platform, which is currently Jenkins, was an easy process as there was a PowerShell plugging for build projects.

While running the script on the build server, there was a problem with vswhere program not returning the installation path. To resolve this problem I added a parameter for passing in the install path of visual studio and to bypass the vswhere step.

A copy of my script and an example of the config file can be found at this location:SSIS Automate Build Files

The reasons for using power shall were as follows:
  • I needed to have the build process to be easy to maintain and could be ported over to any build server platform that runs on Windows.
  • To reduce the risk of us having to revisit the build process due to changes of how SSIS projects are built or the extension library not able to support the build changes.

Comments

Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

SSIS File handle leak in For Each Loop

Cannot update a database that has been registered as a data-tier application