Generating BizTalk binding files with Excel automation in a TFS Build

This is yet another of those things I really need to blog about so that I don’t ever forget this personally


So the background is as follows:

  • A large BizTalk 2010 implementation at a large private Swedish Enterprise customer.
  • Visual Studio 2010 and TFS 2008
  • Developers have already been working more or less 1 year before I arrive at the scene.
  • My mission is to automate the whole build/release process.
  • The devs have written an Excel macro that takes values from the Excel WorkSheet and together with a template-file for BizTalk bindings it generates all the different binding-files for the specified environments in the Excel Worksheet. I’ve seen a lot’s of different ways to do this before and this is yet another.
  • The Excel macro is called from a vbs-file.
  • The vbs-file is called from a powershell script, yes I see a lot of improvement here.

The challenge

So I started my journey today with the hopes of just hooking up the existing scripts for generating the BizTalk binding-files with a TFS build. But for some reason no files were generated but I could see that Excel started and so forth.

After I while I decided to leave the Excel macro thingee and rewrite the complete Excel macro in C#, and I did and it worked just fine.

I hooked up my little console app with my build and got the following message:

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file ‘c:\temp\test.xls’. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Finally! some error I could search for.

The solution

I found the solution here and it stipulates that you should add the following folder structure to the server where you’re running Excel automation:

  • Windows 2008 Server x64
      Please create this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop
  • Windows 2008 Server x86
      Please create this folder: C:\Windows\System32\config\systemprofile\Desktop

It almost felt silly after some 4 hours of rewriting an Excel macro to C# to just add a the “Desktop” folder under C:\Windows\SysWOW64\config\systemprofile\ and the build just worked perfectly!

Warning read this official Microsoft kb about support for this here.

Lessons learned

  • VBA code can be very messy
  • You should start to think about automated build processes from day one in a dev project.
  • I never thought adding a folder could solve this



Building and Unit Testing .Net 4.0 with Team Build 2008

One of the tasks this week was to install and configure a new BizTalk 2010 build server in my clients environment. Pretty straight forward you may say huh? Well it would be if my client was using TFS 2010 but in this case we’re talking about a TFS 2008.

There are lots of posts like this great post explaining how to do this and the building part worked fine. But I kept getting error telling me that unit tests where not able to run because I didn’t have the correct version of Visual Studio installed. Anyway here are the steps I followed to succeed with this endeavor:

  • Install BizTalk 2010 compilation components
  • Installed Team Build 2008
  • Installed Team Build Service Pack 1
  • Configured Team Build 2008 to use MSBuild 4.0 instead of MSBuild 3.5. To do this edit %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\TFSBuildService.exe.config and set the MSBuildPath property to C:\Windows\Microsoft.NET\Framework\v4.0.21006\
  • Restarted the Team Foundation Build service
  • Tried a simple Hello World application without unit tests and the build worked fine.
  • Added some unit tests to the Hello World application and got error the following error MSBUILD : warning : Visual Studio Team System for Software Testers or Visual Studio Team System for Software Developers is required to run tests as part of a Team Build
  • Installed Visual Studio 2010 Premium
  • Got new error telling me that there was some problem with workspace creation during build, found this solution.
  • Changed the property $(COMPUTERNAME)_$(BuildDefinitionId)_$(BuildAgentId) to $(COMPUTERNAME)_$(BuildDefinitionId) in the file %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\TFSBuildService.exe.config.
  • Got back the same old error MSBUILD : warning : Visual Studio Team System for Software Testers or Visual Studio Team System for Software Developers is required to run tests as part of a Team Build
  • Installed Visual Studio Team System 2008 Development Edition
  • Finally the build worked fine and all the unit tests ran successfully!

Hope this will summarize some of the issues you might run into when trying to build and unit test .Net 4.0 applications with Team Build 2008.



End to end TFSBuild pattern for BizTalk 2009, part 3 (Commitment)

So you thought that I would give away the secrets this time huh? I’m just kidding it will all be revealed soon in the last part of these series.

Anyway, how come I make this artistic pause? Anyone? Let’s begin with some basics:

  • Automated builds or continuous integration is useless if the team using it isn’t committed to solving problems with the builds and/or maintaining the builds.
  • Visualize the status of your builds on digital boards, flat screens or with emergency lights/sounds and make sure everyone in the project knows the status of the builds.
  • When all builds are green and all tests are green in your project you are in what I call a normal stage. In a normal stage you should make time for small improvements throughout your team i.e. refactoring, code reviews etc.
  • If any builds are red or any tests are red in your project you are in what I would call a deviate stage. In a deviate stage you should really work as a team to solve the deviations and most importantly learn from it and finally make improvements in how your team works.

Is your team committed to these basics? If not take a good look at the benefits of automated builds:

  • Always the same automated way of compiling, building and deploying your codebase.
  • No more manual releases.
  • One single location for the compiled binaries i.e. the drop folder. Make sure that all deployment flows from the drop folder i.e. establish one deployment method that uses the contents of the drop folder and use it on every environment starting with your own every morning.
  • Automated regression tests.
  • Intact source tree
  • <Enter a clever reason here>

Still not convinced? Well then you should really visit a dev shop where they’re committed to this and compare their speed, quality, agility and team spirit to your own; and I’m sure you will get inspired.

Au revoir