I have been trying to get SQL 2012 SSIS packages built on a TFS2012.2 build system. As has been pointed out by many people the problem is you cannot build SQL SSIS packages with MSBuild. This means you have to resort to call Visual Studio DevEnv.exe from within your build.
Jakob Ehn did a great post on this subject, but it is a little dated now due the release of VS 2012 and SQL 2012
The command line
But before we get to TFS, let us sort the actual command line we need to run. So assume VS2012 is in use, the basic command line build for a solution will be
“C:Program Files (x86)Microsoft Visual Studio 11.0Common7IDEdevenv” “MySolution.sln” /build “Release|Any CPU”
If you solution only contains SSIS packages then this command line might be OK. However you might just want to build a single SSIS project within a larger solution. In this case you might use
“C:Program Files (x86)Microsoft Visual Studio 11.0Common7IDEdevenv” “MySolution.sln” /build “Release|Any CPU” /project “SSISBitsSSISBis.dtproj”
So to work out the command line you need, first you need to make sure VS2012 and the Business Intelligence tools are installed on your build box. Once this is done you can try the command line. I decided for my project that I would create a second solution file in the root of the source code that just contained my two SSIS projects, thus making the command line easier (basically one solution for SSIS packages and another for everything else).
So I ran the command line
“C:Program Files (x86)Microsoft Visual Studio 11.0Common7IDEdevenv” “MySolution.sln” /build “Release|Any CPU”
and got
Microsoft (R) Microsoft Visual Studio 2012 Version 11.0.60315.1.
Copyright (C) Microsoft Corp. All rights reserved.
Error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
Error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
========== Build: 0 succeeded or up-to-date, 2 failed, 0 skipped ==========
Not good. So I checked that if I loaded the same solution in the same copy of Visual Studio 2012.2 it built OK, and it did.
So it seems there is an issue with command line build of SSIS packages in VS2012. A quick search showed it was a logged issue on Microsoft Connect. Luckily a workaround was mentioned, so I tried it, to use the VS2010 version of the tools. So my command line became
“C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDEdevenv” “MySolution.sln” /build “Release|Any CPU”
To try this I had to install the SQL Data Tools from my SQL 2012 ISO (not the SSDT tools from the web as these free ones don’t have the BI features). Once this had installed I could issue my command line and it all built OK.
So I knew I had a working command line. I started to put the same version of VS2010 SSDT tools on my TFS build box and moved onto the build process.
The TFS Build Process
So as now I had the command line, I could apply this knowledge to the process Jakob outlined. There are two basic steps
- Run the command line build – this was basically the same
- Find the files created and copy them to the drops location – the change here is the old post mentions .MSI files, now we are looking for .ISPAC files
As I had decided to have two solutions within my build, I used an if block (based on a solution name convention) to choose if needed to do a MSBuild or DevEnv build. So my process flow for the build phase was.
Also I had to edit the xcopy block to look for .ISPAC files extensions i.e.
Other than these changes the templates was exactly as Jakob described – even down to using VS2010!
Summary
So once all this was done I had a build that create my SSIS packages.
All seems a lot of work, life would be so much easier if SSDT
- Work properly under VS2012
- Or even better support MSBuild!