BM-Bloggers

The blogs of Black Marble staff

Getting SQL 2012 SSIS packages built on TFS 2012.2

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.0\Common7\IDE\devenv”  “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.0\Common7\IDE\devenv”  “MySolution.sln” /build "Release|Any CPU"  /project “SSISBits\SSISBis.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.0\Common7\IDE\devenv”  “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.0\Common7\IDE\devenv”  “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

  1. Run the command line build – this was basically the same
  2. 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.

 

 image

Also I had to edit the xcopy block to look for .ISPAC files extensions i.e.

image

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!

Upgraded to BlogEngine.NET 2.8

I have just upgrade our Blog Server from BlogEngine.NET 2.7 to 2.8, all seems to have gone well, basically it is just file copies as there is no DB schema change, so…

  1. backup your blogs folder
  2. delete it’s contents
  3. copy in the the new release from the zip
  4. fix the SQL connection string
  5. copy around a few files as detailed in the release notes, basically any customisation you have done,

That that seems to be it, just a case now of swapping themes to get the new look, if you want it

Thanks for attending my webinar on lab management

Thanks to everyone who attended my webinar session today in TFS Lab Management, I hope the audio issues were not too much of a problem and you found the session useful. We are looking into the causes of the audio problem so hopefully the next webinar will not need people dialling in via the phone unless they want to.

A number of people asked for the slides, you can find a copy of them here.

As I mentioned in the session if you want a look at Lab Management you can have a go yourself using the HOL in Brian Keller’s TFS 2012 VM. Or watch the video I did at Techday 2010, an end to end demo of Lab Management.

I also mentioned a couple of Microsoft case studies that might be of interest

Our upgrade to TFS 2012.2 has worked OK

I have mentioned in past posts the issues we had doing our first quarterly update for TFS 2012. Well today we had scheduled our upgrade to 2012.2 and I am please to say it all seems to have worked.

Unlike the last upgrade, this time we were doing nothing complex such as moving DB tier SQL instances; so it was a straight upgrade of a dual tier TFS 2012.1 instance with the DB being stored on a SQL2012 Availability Group (in previous updates you had to remove the DBs from the availability group for the update, with update 2 this is no longer required).

So we ran the EXE, all the files were copied on OK. So when we got to the verify stage of the wizard we had expected no issues, but the tool reported problems with the servers HTTPS Url. A quick check showed the issue was the server had the TFS ODATA service bound to HTTP on port 433, but using a different IP address to that used by TFS itself. As soon as this web site was stopped the wizard passed verification and the upgrade proceeded without an errors.

So it would seem that the verification does a rather basic check to see if port 443 is used on any IP address on the server, not just the ones being used TFS as identified via either IP address or host header bindings.

The only other thing we have had to do is upgrade Tiago’s Team Foundation Task Board Enhancer, without the upgrade the previous version of this extension did not work.

So not too bad an experience.

Great event in Antwerp

I had a great time yesterday at the VISUG Conference in Antwerp, thanks to everyone involved in event.

image

The ALM track organiser Pieter and all the speakers, myself, Martin and Neno, were really pleased to see how full our room was all day. There certainly seems to be more interest in ALM than ever before.

If you want to find out more on areas of TFS I was talking on i.e connecting from environments other than Visual Studio, why not look at a very similar session I recorded last year, it can be found on Channel9.

Part of the Global Windows Azure Bootcamp

Have you heard about the Global Windows Azure Bootcamp?  Around the world in 80+ locations, community groups are running an Azure Bootcamp on the 27th April - mainly organised by MVPs, this is an event with over 3000 attendees! From the UK to the USA, in Asia, the Pacific and Australia - liked-minded individuals are coming together to share what they know about Windows Azure.  Supported by the Microsoft Azure team, this is a fantastic opportunity to show the world what the community can do with Azure and how you can get up to speed with developing for it!

Here in the UK, there are at least four Bootcamps taking place, including the event we are hosting here at Black Marble HQ.  If you're free, why not join us?  MVPs Robert Hogg, Steve Spencer, Richard Fennell and Andy Westgarth will be presenting and supporting the attendees throughout the day.

If you have already registered, or plan to, please familiarise yourself with the prerequisites first!

Look forward to seeing you there!

Error TF400129: Verifying that the team project collection has space for new system fields when upgrading TFS to 2012.2

Whist testing an upgrade of TFS 2010 to TFS 2012.2 I was getting a number of verification errors in the TFS configuration upgrade wizard. They were all TF400129 based such as

TF400129: Verifying that the team project collection has space for new system fields

but also mention models and schema.

A quick search threw up this thread on the subject, but on checking the DB tables I could see my problem was all together more basic. The thread talked of TPCs in incorrect states. In my case I had been provided with an empty DB, so TFS could find not tables at all. So I suppose the error message was a bit too specific, should have been ‘DB is empty!!!!’ error. Once I got a valid file backup restored for the TPC in question all was ok.

A bit more digging showed that I could also see an error if I issued the command

tfsconfig remapdbs /sqlinstances:TFS1 /databaseName:TFS1;Tfs_Configuration

As this too reported it could not find a DB it was expecting.

So the tip is make sure you really have the Dbs restored you think you have.