Continuous Integration with TFS2010, MSDeploy & VSDBCMD

After I created an account at Cytanium, I quickly discovered the glory of One-Click Publish. After setting up my source control in TFS. All I could think of, was getting TFS to do the same thing for me, but even I knew that wouldn’t be enough. I wanted TFS to deploy my database changes as well. Well, there is that Schema Compare utility in Visual Studio, I know it can figure out and deploy changes to a database. I figured if Visual Studio can do all of these things, I should be able to as well… shouldn’t I?

Custom Build Process Templates

Very soon after you start to ask these kinds of questions, you find yourself looking at TFS Build Process Templates. The templates are designed in Windows Workflow and thanks to a few good blogs out there this is a rather approachable topic. Most of my starting information came from this blog sequence: Ewald Hofman’s – Customize Team Build 2010.

Before you start, save yourself the pain, take Ewald’s advice and add the Team Foundation Build Activities to your Toolbox. This will shave 15 seconds off the time to open every template.

Exposing Arguments and Providing Metadata

More lessons from Ewald on adding arguments and adding more complex arguments. We will use them and see how the values are supplied a bit later on.

  • Continuous Integration Database Deployment
    • ciVSDBCMDConnectionString: “Database Connection String”
    • ciVSDBCMDManifestFile: “Database Manifest File”
    • ciVSDBCMDTargetDatabase: “Target Database”
  • Continuous Integration Website Deployment
    • ciMSDeployWebProject: “Web Project”
    • ciMSDeployUsername: “MSDeploy Credentials: Username”
    • ciMSDeployPassword: “MSDeploy Credentials: Password”
    • ciMSDeployWebService: “MSDeploy Web Service Url”
    • ciMSDeployWebApplication: “Web Application Name”

Customizing the Template

Under your Team Project, go to the folder BuildProcessTemplates, and branch DefaultTemplate.xaml to ContinuousIntegrationTemplate.xaml.

Open it up and and take a look around. It will help if you take a top down approach, Collapse All, using the controls in the top right. Locate the AgentScope Activity named “Run On Agent” and expand it. Now look for the open space after the “Try Compile, Test, and Associate Work Item” Sequence Activity; this is where we will start adding our custom functionality.

In this space add a If Activity and name it: “If Build and Test Succeeded”. In its condition we are going to add the following VB statement:

BuildDetail.CompilationStatus = BuildPhaseStatus.Succeeded And
    (BuildDetail.TestStatus = BuildPhaseStatus.Succeeded Or
        BuildDetail.TestStatus = BuildPhaseStatus.Unknown)

In the Then clause we drop a sequence named: “Deploy Database and Website”. Inside that sequence, go ahead and create a “Deploy Database” Sequence and a “Deploy Website” Sequence. In the else clause we can create a WriteBuildWarning with the message: “Deployment Skipped”. It should all look like this in the end…

Deploy Database

A large part of my introduction to VSDBCMD (not to mention a large part of this idea) came from this Visual Studio Walkthrough on the topic. If you are willing to work through the various issues with using Database Deployment, it is worth it.

I happen to have Visual Studio installed on my TFS Server, if you dont, copy this application from your Visual Studio install to all your TFS Build Agents.

C:Program Files (x86)Microsoft Visual Studio 10.0VSTSDBDeploy

We are going to use an InvokeProcess Activity to call VSDMCMD.exe with the following code for arguments property. The manifest file will contain the Database name and Connection String that was configured in the project, but chances are you are going to want to override this per environment.

String.Format("/a:Deploy /dd+ /dsp:Sql" _
     & " /manifest:""{0}{1}""" _
     & " /p:TargetDatabase=""{2}"" /cs:""{3}""", _
     BuildDetail.DropLocation, ciVSDBCMDManifestFile, _
     ciVSDBCMDTargetDatabase, ciVSDBCMDConnectionString)

Be sure to properly name the InvokeProcess Activity, as well as provide WriteBuildMessage and WriteBuildError Actvities for the standard and error output.

Deploy Website

Deploying databases was actually a lot easier than I expected, which is fortunate, because deploying websites was a lot more challenging.

Lets add a few variables to the Deploy Website Sequence:

  • Name: websitePublishDirectoryPath
  • Variable type: String
  • Default:
    String.Format("{0}Website Deployment", BinariesDirectory)
  • Name: msDeployManifestFilePath
  • Variable type: String
  • Default:
    String.Format("{0}{1}.manifest", websitePublishDirectoryPath, ciMSDeployWebProject)

As we will continue by defining a Sequence named “Prepare Deployment”. This sequence is responsible for preparing a folder to publish. So we have to create the directory copy the output to it and perform any Web.config transformations. First lets declare a variable in this scope:

  • Name: platformConfiguration
  • Variable type: Microsoft.TeamFoundation.Build.Workflow.Activities.PlatformConfiguration
  • Default:
  • Name: websiteSourceDirectory
  • Variable type: String
  • Default:
    String.Format("{0}_PublishedWebsites{1}", BinariesDirectory, ciMSDeployWebProject)
  • Name: websiteDestinationDirectory
  • Variable type: Microsoft.TeamFoundation.Build.Workflow.Activities.PlatformConfiguration
  • Default:
    String.Format("{0}{1}", websitePublishDirectoryPath, ciMSDeployWebProject)

One CreateDirectory Activity to create the folder named in variable `websitePublishDirectory`, one CopyDirectory Activity to copy `websiteSourceDirectory` to `websiteDestinationDirectory` and an additonal Sequence named “Find and Transform Web.config Files”

Click for the full workflow
Yet Another Web.config Transform

There seems to be a lot of places to find good information on the Web.config
transformation topic. So I’m not reinventing anything here. Generally I looked for web.config files and any matching transformation files. An MSBuild Activity is used to perform the transformation. Finally delete all transform files, and continue. It’s important, but rather boring… and this blog post is long enough already.

Web Deploy 2.0

Hopefully you can have someone else configure Web Deploy 2.0 for you, but since you are reading this, I doubt that is the case. On, I found the best set of tutorials to install, configure, (and most importantly) debug. If you are having problems with the setup, don’t hesitate to enable tracing of failed requests. Once you are able to deploy with Visual Studio you are ready to continue… right? I mean, the MSDeploy command doesn’t seem too intimidating. After a day and change, I was ready to scream uncle. Until I found this StackOverflow Question, I was unable to figure out the arguments of the command that Visual Studio used to initiate the deployment.

First we have to generate a msdeploy manifest file. An InvokeMethod Activity to call `System.IO.File.WriteAllText` with the following expression

String.Format("<?xml version=""1.0"" encoding=""utf-8""?>" _
    & "<sitemanifest>" _
        & "<contentPath path=""{0}{1}"" />" _
        & "<setAcl path=""{0}{1}"" setAclResourceType=""Directory"" />" _
        & "<setAcl path=""{0}{1}"" setAclUser=""anonymousAuthenticationUser"" " _ & "setAclResourceType=""Directory"" />" _
    &"</sitemanifest>", _
    websitePublishDirectoryPath, ciMSDeployWebProject)

Finally we can execute MSDeploy with an InvokeProcess Activity and the following arguments.

String.Format("-source:manifest='{0}'" _
              & " -dest:auto,ComputerName='{1}?site={2}',UserName='{3}',Password='{4}',IncludeAcls='False',AuthType='Basic'" _
              & " -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateExtension" _
              & " -setParam:kind='ProviderPath',scope='contentPath',match='^{5}\{6}$',value='{2}'" _
              & " -setParam:kind='ProviderPath',scope='setAcl',match='^{5}\{6}$',value='{2}' -allowUntrusted", _
              msDeployManifestFilePath, ciMSDeployWebService, ciMSDeployWebApplicationName, _
              ciMSDeployUsername, ciMSDeployPassword, _
              websitePublishDirectoryPath.Replace("", "\").Replace(" ", " ").Replace(".", "."), _
              ciMSDeployWebProject.Replace(" ", " ").Replace(".", "."))
Configure and Queue the Build

We can finally see the light at the end of the tunnel here. Since we provided the metadata for our arguments, we get a rather impressive display of customization.

Queue up your build and hopefully you wont have to do too much debugging to get this to work for yourself.


After seeing this work, it was all worth it. Of course, this is all very custom, I have my xaml attached here as an example, but you will have to be prepared to work with it to make it work for you. Drop me a line, I’ll help you out if I can.