Automating Deployment of SSIS – 1) Building the Project

If you’re just getting started with Continuous Integration (CI), or thinking about it, this post will show the steps to building your SSIS Projects.

Why Build?

Once you have a CI server running (, TeamCity, Jenkins, CruiseControl, to name a few…), you will need to decide whether the build server does the building of the projects or if the team will check in compiled project files.  The consequence of this is really whether you install all of your development software on the server or not.  There are several good reasons to install it:

  • Users can forget to compile the code or to check-in the compiled output which could mean that the code is up to date, but the output (ispac file) is not
  • Build errors are valuable feedback to the developer.  After all, failing early is the point of CI in the first place!
  • Check-ins from other users could cause build errors, though this issue is mitigated by building on each check-in.  If you are building after multiple check-ins, building on this server ensures that all the code that is checked into source control actually builds.

Some Nuances for SSIS

For SSIS, building a project creates output (an .ispac file) in the bindevelopment directory.  While the rest of the visual studio-using world uses configurations of “debug” and
“release,” the SSIS team decided to do it completely differently.  If you include other projects in your solution (like an nunit test project), the best thing to do is to add a “development” configuration to that project.
The reason for the above recommendation is that SSIS is really funny about these configurations.  If you add a “release” one in the configuration manager to the SSIS project, it will build based on whatever state you leave the configuration manager window in, not on what the active configuration is or the build parameter on the command line (seriously, I’ve tested this)!

Knowing this and not knowing the way that any user might leave that configuration window, it is better to foolproof the problem and only leave one possible configuration.
In addition to this, the build process does little more than create the ispac file.  If there are validation errors in the package, it will still be packaged up anyway.  Some severe errors may stop the build like a reference to an invalid object.  I guess that somewhat rules out the point above feedback, but it’s still a good practice to perform the build on the server for the other reasons.  Additionally, I highly suggest automated testing following the automated build for improved feedback.
For the commandline argument, we can use use  located in C:Program Files (x86)Microsoft Visual Studio 12.0Common7IDE.  This tool will provide some of the build feedback in the standard output, which is great for troubleshooting.  However, I did have some issues with it throwing this error on the build server:
MyProject.dtproj : error : Operation is not valid due to the current state of the object.
One or more projects in the solution were not loaded correctly.
Please see the Output Window for details.
The operation could not be completed. The parameter is incorrect.
Not sure what exactly the problem was but I could not reproduce it locally.  So I wound up using devenv.exe on the build server.  Unfortunately, that hides the output, but at least it builds.

Final Command

Finally, here is the command to build a project in powershell:

$devenv = ${env:programfiles(x86)}Microsoft Visual Studio”


& $devenvC:MySsisProject.sln/Build “Development” 

A couple of things to note, you can use “Rebuild” to delete the compiled project and create a fresh version of the file, but there is no “Clean” like in other VS projects.  Also, if you build a project file, it will build the solution (all projects will be built).  From here, you just need to package up the ispac file with your build artifacts.  Depending on the build server you are using, this could just be another powershell script grabbing the file.
Next up, automating deployment.

Share on

linkedin sharing button twitter sharing button