Tag Archives: SSDT

Adventures getting MSBuild, TFS and SQL Server Data Tools to work together

We recently found that our database project at work goes from a 40-minute build and compile to about 20 minutes when we upgrade from the VS2010 (SQL 2008) database projects (with the old .dbproj files) to the new SQL Server Data Tools (SSDT) projects with the .sqlproj files, even though we’re still deploying to SQL Server 2008 R2. So our goal immediately became:

Get the SSDT projects to compile with parameters from MSBuild

The problem is that with the new .sqlproj and the SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets there’s no built-in way to pass the database name or even a connection string when building the project via MSBuild — which is a critical part of our continuous integration builds. The project I’m working on these days has 7 or 8 teams working in up to twice that many branches, and all those branches need CI builds, every one of which deploys the database project and validates it. Since the branches change at least once a week, it’s way too much work to run around modifying publish.xml files to change database names every time we create a new branch (which we need to do to avoid the builds deploying over the top of each other).

With the old .dbproj format, there was a SQLDeploy task called in the TeamData\Microsoft.Data.Schema.SqlTasks.targets build target file which included a whole bunch of variables that could be overridden on the command line, so we could pass TargetDatabase and TargetConnectionString as MSBuild arguments, and then, to be able to compile the whole solution and still call the Deploy target, we added this to the project file:

    <DBDeployOnBuild Condition="'$(DBDeployOnBuild)' == ''">False</DBDeployOnBuild>
  <Target Name="AfterBuild">
    <CallTarget Targets="Deploy" Condition="'$(DBDeployOnBuild)'=='True'" />

In our workflow, we redefine MSBuildArguments in the workflow, and now we can msbuild the whole solution and the database will be deployed:

MSBuildArguments & " /p:DBDeployOnBuild=True;TargetDatabase=""" & BuildDetail.BuildDefinition.Name & """;TargetConnectionString=..."

But that doesn’t work with the new SSDT project type.

First of all, they don’t deploy with all their dependencies, instead, they have to be published. It’s basically the same thing, with a different name. But the SqlPublish task requires all the parameters to be in an xml file, and there’s no build properties we can override, because the properties are hiding in that publish.xml file that doesn’t get tokenized

I’ve spent the last couple of days figuring out a work around, so I figured I should blog it up here and help the next guy. The process is not simple. The bottom line is that I haven’t found a way to get the SQLPublish task to take it’s values from anywhere except a publish xml file, so the solution I came up with was to rewrite the publish file using the XDT transform tasks defined for Web.config transforms.

Web.Config Transforms, on random XML files

The cool thing is, there’s actually a ParameterizeTransformXml task which allows you to define your transform as a string in the build file.

  <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" />

In web projects, that task is used to replace connection strings (to hide them in web packages), but we can use it to replace the database name in our publish.xml. In fact, I can actually add the same properties to the build that we used to have with the old project format (and which we’ll define on the command-line, exactly the way we did before). We put some default properties for TargetDatabaseName and TargetConnectionString in our Debug.publish.xml and our CI.publish.xml and then we just replace them during the build.

It’s a lot more complicated than what we had to do previously, partly because we need to define the SqlPublishProfilePath for the Publish task, but we have to use CallTarget to call the Publish target (not Deploy this time), which doesn’t support passing properties, nor does the target you call inherit properties that are defined in your scope. This means we need to define the SqlPublishProfilePath property in the BeforePublish target which the publish target depends on (the “dependson” relationship inherits defined properties).

  <Target Name="AfterBuild" Condition="'$(DBDeployOnBuild)'=='True'">
    <CallTarget Targets="Publish" />
  <Target Name="BeforePublish" Condition="('$(TargetDatabase)' != '' Or '$(TargetConnectionString)' != '') And Exists($(TransformOutputFile))">

But the real work is actually setting up the TransformPublishXml property with the right XML to replace the nodes with the properties from the command-line arguments, and then actually calling the task. Since we imported the task before, we just need a property group to define our variables with default values, and then a BeforeBuild target to actually call the ParameterizeTransformXml:

  <PropertyGroup Condition="'$(TargetDatabase)' != '' Or '$(TargetConnectionString)' != ''">
    <DBDeployOnBuild Condition="'$(DBDeployOnBuild)' == ''">False</DBDeployOnBuild>
    <TargetConnectionStringXml Condition="'$(TargetConnectionString)' != ''">
      &lt;TargetConnectionString xdt:Transform="Replace"&gt;$(TargetConnectionString)&lt;/TargetConnectionString&gt;
    <TargetDatabaseXml Condition="'$(TargetDatabase)' != ''">
      &lt;TargetDatabaseName xdt:Transform="Replace"&gt;$(TargetDatabase)&lt;/TargetDatabaseName&gt;
    <TransformPublishXml>&lt;?xml version="1.0"?&gt;
        &lt;Project xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"&gt;
    <TransformFile Condition="'$(SqlPublishProfilePath)' != ''">$(SqlPublishProfilePath)</TransformFile>
    <TransformFile Condition="'$(SqlPublishProfilePath)' == ''">$(Configuration).publish.xml</TransformFile>
    <TransformFile Condition="'$([System.IO.Path]::IsPathRooted($(TransformFile)))' == 'False'">$(MSBuildProjectDirectory)$(TransformFile)</TransformFile>
    <!-- In order to do a transform, we HAVE to change the SqlPublishProfilePath-->
    <BuildDefinitionName Condition="'$(BuildDefinitionName)' ==''">VSBuild</BuildDefinitionName>
    <TransformStackTraceEnabled Condition="'$(TransformStackTraceEnabled)'==''">False</TransformStackTraceEnabled>
  <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" />
  <Target Name="BeforeBuild" Condition="('$(TargetDatabase)' != '' Or '$(TargetConnectionString)' != '')">
    <Message Text="The Target Database: '$(TargetDatabase)' and Connection String: '$(TargetConnectionString)'" Importance="high" />
    <!-- If TargetDatabase or TargetConnectionString is passed in
        Then we use the tokenize transform to create a parameterized sql publish file-->
    <Error Condition="!Exists($(TransformFile))" Text="The SqlPublish Profile '$(TransformFile)' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'" />
    <ParameterizeTransformXml Source="$(TransformFile)" IsSourceAFile="True" Transform="$(TransformPublishXml)" IsTransformAFile="False" Destination="$(TransformOutputFile)" IsDestinationAFile="True" Scope="$(TransformScope)" StackTrace="$(TransformStackTraceEnabled)" SourceRootPath="$(MSBuildProjectDirectory)">

So all you have to do is put those three blocks of XML at the bottom of your .sqlproj file, and then call msbuild with /p:TargetDatabase=DBName;TargetConnectionString="Data Source=DBServer;User ID=sa;Password=password";DBDeployOnbuild=True to get the database project to build and deploy to the database you want.

If you’ve got questions, post ‘em — I’m writing this at 1:30 in the morning so I’m not at my most lucid :-)