Just A Programmer We're just programmers


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 learn.iis.net, 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.


  • Pingback: Stanley Goldman

  • Pingback: Stanley Goldman

  • Pingback: Stanley Goldman

  • Rajeshkannan Krish


    I tried the template for only deploy the database using VSDBCMD.
    I got the error message like “The directory name is invalid”

    could you please help me to resolve this issues.

    • Anonymous

      Hey Rajesh,

      Are you sure you have VSDBCMD deployed to your TFS Server?
      It only comes with a Visual Studio installation.

  • Mordechai Danielov

    You can also build and deploy with any scripting language e.g. Powershell. This is especially useful for any DB projects that have more than a dozen objects in them. Since I like my checkins to be clean and don’t want to stop working in VS while the thing is building a project, I run msbuild with a powershell script. and if I wish to deploy it, I have another script that will run vsdbcmd.exe

  • Mordechai Danielov
  • Chad Boettcher


    First of all, thanks for the great post, it’s precisely what I was looking for.

    I am using the web deployment section from your sample xaml file and it is working up until the Get Transformation Project task within the Perform Transformation sequence.  The variable transformationProjectServerFile contains the default value “$/SampleProject/BuildProcessResources/Targets/TransformWebConfig.proj”.  What is this project and is it possible to obtain the source for this if it is required?Thanks,Chad
    “$/SampleProject/BuildProcessResources/Targets/TransformWebConfig.proj”.  What is this project and is it possible to obtain the source for this if it is required?



    • gimmer

      I, too, had this same question. What’s the content of this .proj file? Or was this just something to hold a list of targeted items to transform?  Like from here? 
      http://msdn.microsoft.com/en-us/library/dd465326.aspx  …of course, the msbuild/deploy process can do that for us when we pre-define those transforms in, say, the Web.Debug.config file.

      • StanleyGoldman

        @gimmer:disqus & @google-b07e90eaa55184e52611b7d1da9ba4ae:disqus

        You guys are right
        It was just something I fashioned from that functionality…


        • gimmer

          Hi Stanley,

          Thank you for sharing that! However, I’ve been fighting with a 401 error when trying to contact my target system using this method.It’s the same target, using the same credentials, and the same web deploy service URL that worked with prior(successful) attempts with MSBuild. Did you have any authorization issues?

  • Mihir Shah


    This is a great post indeed. Its very helpful.I am using Database deploy part of this post and using the template provided by you.But my concern is, I dont want to deploy the database on every successful check-in.I want the deployment to be done only when user wants (when specific event occurs/user initiated).Is there any way to achieve this?Thanks N Regards,Mihir

  • Pingback: Database Build & Deployment with Team Foundation Server (TFS)

  • Pingback: Automatically deploying data dude projects with vsdbcmd.exe | BitWise MnM Inc.