SQL Server Integration Services (SSIS) is a tool widely used by many companies. Unfortunately, it has been initially built a long time ago, back at the time when Build processes and Continuous Integration (CI) were uncommon. Since then, SSIS hasn’t been mostly changed. The only changes were - project deployment model since SQL 2012 and compatibility with new versions of SQL Server and Visual Studio shell.

Therefore the whole mentality of the SSIS toolset remained developer workstation oriented. Deployment package compilation and server deployment was done from a Visual Studio shell. MSBuild does not support SSIS Projects.

A few weeks ago I decided to automate the SSIS projects build and deployment. I came across a sample provided by Microsoft at http://sqlsrvintegrationsrv.codeplex.com/ called SSMSMSBuild. Initially I was planning on using just that, but then quickly realized that it is not what I wanted. First, you have to mock an msbuild compatible project file. Second, you should compile a dll from the solution above, and place it into a Program Files subfolder, where your SSDT installation is, and even then all what msbuild does - simply passes control and parameters to a method in that dll. Plus of course, the sample project was outdated and did not support versions other than SQL 2012, did not support encryption, and many other things, not to mention several critical bugs in that code. So I decided to create just a standalone SSIS Build utility that does what I need.

After a few night and weekends of development, I wrote this utility. It is capable of handling SQL 2012 and 2014 projects, including decryption and reencryption, custom parameters, release notes parsing, and other features. I still need to add support for reusable flow parts in SQL 2016.

The github repository is here: https://github.com/rtumaykin/ssis-build. Inside there is a working sample, including build and deploy scripts, as well as instructions. The tool is distributed as a Nuget package and the build script simply downloads on demand and executes the tool with all necessary parameters.

In my next post I will dive deeper into deployment, including automatic installation of custom components assemblies into GAC.