SQL Server Logo

Automating build of Integration Services projects

After our experiences with the build process in Analysis Services – expectations were low when we turned our gaze towards Integrations Services (SSIS).

Our overall experience with SSIS has generally been positive – it’s an extremely powerful tool with some interesting performance characteristics. There are some annoying flaws – especially in the Visual Studio integration. When I work with SSIS projects – three or four daily crashes of Visual Studio is not uncommon. Never fear – the power provided by the tool more than makes up for this when it comes to choosing the ETL platform. And buggyness is what service packs are for … isn’t it? 🙂

The first thing you notice when you choose “build” in Visual Studio on an SSIS projects is that Visual Studio doesn’t do anything – except of course copy files to the bin directory. Why? Well, guess you could say that the *.dtsx files contain all the information need to run the packages – there is “nothing to be done”.


But, when you try to automate a build there actually is something to be done. First of all: you have to mark which files contain the build output. That is, the files you wish to copy to your deployment target. With this in mind – we tried using msbuild on the project – hoping that it would at least mark the *.dtsx files as build output (it could just take a look at the bin directory for us) so Team Foundation can copy them to the release directory. No surprises – integration is non existent and there are no free meals here.

Guess you could say that the command line to automate build of SSIS is simple: just copy the bin*.dtsx files to your build output directory.

We could have stopped here and be done with it – but we like build logs (if you can use the about the word: “like” in this context). Those of you who have deployed a big BI-installation by hand may have noticed the excellent command line tool; dtexec.exe used to run SSIS packages. This tool has some very useful parameters that come in handy for the structured developer.

When you execute an SSIS package – two things happen:

  • The package is validated. During this phase, connection managers and logs providers are tested and metadata for all tasks and flow components is checked for inconsistencies
  • The package is executed. There seems to be some sort of compilation going on – part of this is probably done in the validate phase. After this compilation – the package is executed. The compilation part is an educated guess – I cannot believe SSIS can be as fast as it it without first compiling the package to machine code.

Here’s the trick: The first phase produces both warnings and errors if you have made a mistakes in your package design. This is a very useful output and can be considered a build log of the package. Fortunately – its possible to invoke the first phase without executing the entire package. You simply do this:

dtexec /F MyPackage.dtsx /Va /Rep EW

/Va makes the package validate but not execute

/Rep EW tells SSIS that you only want to see warnings and errors on stdout. If you don’t use the /Rep switch you will clutter your “build log” with useless informational messages.

Summary: to build an integration services project you do two things:

  • Execute dtexec with proper command line switches on all your *.dtsx files. Save stdout from these command lines to your build log.
    • If the build log contains any errors – the sadistic among you may want to email notificaty to the responsible developer. It gives him a chance to get up in the middle of the night and fix the bug 🙂
  • Copy all bin/*.dtsx files to you build output directory