locked
SSDT project integration into TFS Build RRS feed

  • Question

  • Hi,

    You guys are making a great product, but there is not much information on how to integrate bits and pieces into TFS Build server.

    I have SSDT-DP CTP4 installed on a VM that runs our TFS builds.

    So my SSDT-DP project builds OK on a build server and I can publish build from commandline, using syntax like this:

    sqlpackage.exe /a:Publish /sf:D:\Builds\1\Project\DatabaseBuild\Binaries\Database.dacpac /pr:D:\Builds\1\Project\DatabaseBuild\Sources\Database\Build.publish.xml /tu:username /tp:password

    The question is how to integrate DB publishing with TFS build server so it would happen automatically on every build?

    I tried adding MSBuild Arguments: /p:DeployToDatabase=true, but I get error: "The deployment manifest file Database.deploymanifest does not exist"

    Do you have some special Build process template for SSDT-DP projects?

    Thank you.

    Wednesday, January 18, 2012 7:20 AM

Answers

  • Ok, so finally I sorted out build. I think there was problem with sources and outputs not being cleaned up properly and incorrect msbuild arguments.

    So now I have something like this, which builds and deploys DB:

    I use "Default Template" for build process.

    Then in Process > Advanced build process parameters > msbuild arguments I specify targets and profile:

    /t:Build;Publish /p:SqlPublishProfilePath=Build.publish.xml

    This Build.publish.xml file is in source control and in my case is using sql credentials:

    <?xml version="1.0" encoding="utf-8"?>
    <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
      <PropertyGroup>
        <TargetDatabaseName>DatabaseName</TargetDatabaseName>
        <DeployScriptFileName>Script.database.sql</DeployScriptFileName>
        <TargetConnectionString>Data Source=SQLServer;Persist Security Info=True;User ID=userName;pwd=passWord;Pooling=False</TargetConnectionString>
        <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
        <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
        <PublishDependentProjects>False</PublishDependentProjects>
        <ProfileVersionNumber>1</ProfileVersionNumber>
        <SingleUserMode>False</SingleUserMode>
        <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
        <DropObjectsNotInSource>False</DropObjectsNotInSource>
      </PropertyGroup>
    </Project>

    • Marked as answer by al3nas Thursday, April 12, 2012 1:32 PM
    Thursday, April 12, 2012 1:31 PM

All replies

  • In CTP4 you need to install SSDT on the Team Build Server, for RTM we added a new installer which just installs the build tasks and target file for this exact reason.

     


    -GertD @ www.sqlproj.com
    • Proposed as answer by Janet Yeilding Friday, January 20, 2012 1:14 AM
    Thursday, January 19, 2012 5:37 PM
    Answerer
  • hi Gert,

    any chance that the tasks and target file will just get bundled into an off-the-shelf TFS installation in the future?

     

    thanks

    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Thursday, January 19, 2012 5:46 PM
  • Maybe, as we are moving in to a mode where we will update the tooling every 4-6 months to keep up with SQL Azure changes, this could create more of a logistics problem than a win. No commitment.

     


    -GertD @ www.sqlproj.com
    Thursday, January 19, 2012 9:11 PM
    Answerer
  • I did install SSDT on Team Build Server.

    But it is not clear to me how to publish Database from Build server on automated build.

    Friday, January 20, 2012 1:59 AM
  • You should be able to use the MSBuild SqlPublishTask

    <!--
       Target Definition: SqlPublish
       Dependency Of:     Publish
       -->
      <PropertyGroup>
        <SqlPublishDependsOn>
          _SetupSqlPublishInputs;
          _SetupSqlPublishOutputs
        </SqlPublishDependsOn>
      </PropertyGroup>

      <Target Name="SqlPublish"
              Inputs="@(SqlPublishInputItem)"
              Outputs="@(SqlPublishOutputItem)"
              DependsOnTargets="$(SqlPublishDependsOn)">

        <SqlPublishTask
          CacheTargetModel="$(CacheTargetModel)"
          ContributorArguments="@(DeploymentContributorArgument)"
          PublishProfile="$(SqlPublishProfilePath)"
          ScriptFile="@(PublishScriptFile)"
          SourceModel="@(SqlTarget)"
          UpdateDatabase="$(UpdateDatabase)" />

      </Target>


    -GertD @ www.sqlproj.com
    • Marked as answer by Janet Yeilding Tuesday, January 31, 2012 12:12 AM
    • Unmarked as answer by al3nas Tuesday, April 10, 2012 12:42 PM
    Friday, January 20, 2012 3:11 AM
    Answerer
  • I am a noob regarding MSBuild and TFS build.

    But I am getting there slowly.

    I added Build and Publish targets to MSBuild arguments and also specified publish profile and target connection string. Both publish profile and target connection string have sql user/password, but MSBuild throws error that it "could not connect to target database". Though in a log I can see that build is trying to use Build\Database.publish.sql, which does not exists.

    MSBuild argumets are something like this (i am typing from memory):

    /t:Build;Publish /p:SqlProfilePath=FileName.publish.xml /p:TargetConnectionString="Connection String"

    I think I have to look at Build Template. Maybe publishing rules are there (I am using Default.....xaml template).

    Friday, January 20, 2012 3:21 PM
  • Is there any documentation on SqlPublishTask ?
    Friday, March 30, 2012 12:19 PM
  • I did not find any documentation...And I still have trouble with publish on a build server...

    I updated to SSDT RTM and if I publish using VS on build machine - everything works fine, but build server can not publish.

    I noticed that I have to check "Create script (.sql file)" in Database>Properties>Project Settings, otherwise build log shows error that it can not find .sql file.

    After examining DbUpdate_Create.sql file I can see that it sets incorrect variables. So it does not pick up those variables from publish profile...weird:

    :setvar DatabaseName "Database"
    :setvar DefaultFilePrefix "Database"

    and the error is:

    D:\Builds\1\solution\DatabaseBuild2\Binaries\Database.publish.sql: Unable to connect to target server.

    Obviously if it is using db name as "Database" it will not be able to connect. But there might be another problem. It shows "Database.publish.sql", but that file does not exist in Binaries (and anywhere else) - it creates DbUpdate_Create.sql

    I think there is something wrong somewhere, but not sure what is wrong...

    Tuesday, April 10, 2012 12:40 PM
  • Ok, so finally I sorted out build. I think there was problem with sources and outputs not being cleaned up properly and incorrect msbuild arguments.

    So now I have something like this, which builds and deploys DB:

    I use "Default Template" for build process.

    Then in Process > Advanced build process parameters > msbuild arguments I specify targets and profile:

    /t:Build;Publish /p:SqlPublishProfilePath=Build.publish.xml

    This Build.publish.xml file is in source control and in my case is using sql credentials:

    <?xml version="1.0" encoding="utf-8"?>
    <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
      <PropertyGroup>
        <TargetDatabaseName>DatabaseName</TargetDatabaseName>
        <DeployScriptFileName>Script.database.sql</DeployScriptFileName>
        <TargetConnectionString>Data Source=SQLServer;Persist Security Info=True;User ID=userName;pwd=passWord;Pooling=False</TargetConnectionString>
        <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
        <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
        <PublishDependentProjects>False</PublishDependentProjects>
        <ProfileVersionNumber>1</ProfileVersionNumber>
        <SingleUserMode>False</SingleUserMode>
        <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
        <DropObjectsNotInSource>False</DropObjectsNotInSource>
      </PropertyGroup>
    </Project>

    • Marked as answer by al3nas Thursday, April 12, 2012 1:32 PM
    Thursday, April 12, 2012 1:31 PM
  • Is there a way to override and/or set the TargetConnectionString property on the command line and not have it be set in the publish.xml file? I dont want to check the connection string into source control; the build system will set it. When I use this code I always get an error saying the connection string is incorrect.

    Ideas?

    CT

      <Target Name="DeployDatabase">
        <PropertyGroup>
          <ConnectionString>Server=$(DatabaseServer)%3BTrusted_Connection=true</ConnectionString>
        </PropertyGroup>
        <MSBuild 
          Projects="$(DatabaseProject)"
          Properties="Configuration=$(Env);SqlPublishProfilePath=$(Env).publish.xml;TargetConnectionString=$(ConnectionString)" 
          Targets="SqlPublish" 
          ContinueOnError="false"/>
      </Target>

    Friday, April 13, 2012 7:43 AM
  • You suppose to be able to override TargetConnectionString by adding this to msbuild arguments, but somehow I was having problems with this:

    /p:TargetConnectionString="Connection String"

    Friday, April 13, 2012 8:38 AM
  • Is there really no way to do this?  I've tried everything I can think of, but that publish.xml file just doesn't seem to pick up build variables from the other build scripts.

    We're desperate to get this working because I've got 8 teams working on multiple feature branches each week all trying to deploy the same database --with the old db tools I can pass the database name on the command-line, so the fact that we're deploying 20 or so different databases in our CI builds isn't a problem ...

    Please tell me that migrating to SSDT isn't going to require creating dozens of deploy scripts every week ...

    Friday, April 20, 2012 9:36 PM
  • From what I've seen, you need to create a specific publish.xml per branch/build.

    Last week we used SqlPackage.exe in commandline through the build to deploy database. Was successful, but calling that from build is not cool, right ;-)

    There's no info in the SqlPublishTask which would be an msbuild task, so don't know where to start with that. 
    I did see this where you can specify a publish.xml file msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.publish.xml in Gert's post http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/   

    Saturday, April 21, 2012 8:25 AM
  • I guess I am just not ready to accept a solution that requires me to create one of those publish.xml files for every single feature branch ... just so I can change the database name to match the CI build definition name to avoid collisions.
    Tuesday, April 24, 2012 5:33 PM
  • This solution is good enough for me.

    Thanks!


    Deepak

    Saturday, April 28, 2012 12:47 AM
  • Is there anyway to have the publish Task not deploy from the build server.  Just have it create the script and store it in the output folder?

    Ihave these parameter now and it deploys but I really just want it to create the script.  Ihave another process that would pick up that script and do what I need it to do:

    /t:Build /t:Publish /p:SqlPublishProfilePath=Script.Publish.xml /p:Deploy=False ????

    Wednesday, May 2, 2012 7:36 AM
  • Just out of interest, are people choosing to use SqlPublishTask or sqlpackage.exe? I have defined my DB publish in an msbuild script like so:

      <Target Name="SSDTDeploy">
        <Message Text="**************Publishing Database project: %(SSDTProj.Identity)**************" />
        <Exec Command="&quot;$(PROGRAMFILES)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe&quot; /a:Publish /sf:%(SSDTProj.Path)\bin\$(CONFIGURATION)\%(SSDTProj.Identity).dacpac /pr:%(SSDTProj.Path)\Publish\$(ENVIRONMENT).publish.xml" />
      </Target>

    Main reason I chose to do that is that I used to use vsdbcmd.exe in the same way and I've never had any problems with it so it was just natural to carry on using its replacement, sqlpackage.exe.

    P.S. It won't be much consolation to Joel (sorry Joel) but FYI I am loving these new .publish files as opposed to the old method of defining all the parameters on the command-line.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Wednesday, May 2, 2012 8:12 AM
  • Is there anyway to have the publish Task not deploy from the build server.  Just have it create the script and store it in the output folder?


    Hi Ryansp,

    Is it possible to affect the action? If using sqlpackage.exe there is an action property that allows you to specify "script" as opposed to "publish" - I am assuming there is something similar for SqlPublishTask.

    Regards
    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    • Proposed as answer by Ryan Perlman Wednesday, May 2, 2012 9:46 AM
    Wednesday, May 2, 2012 8:14 AM
  • Thanks Jamie,

    I will look into that option as well, in the mean time I have done this after looking at the Microsoft.Data.Tools.Schema.SqlTasks.targets file in the C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT directory.

    Added one additional property to my MSBuild Properties for Team Build which now look like this:

    /t:Build /t:Publish /p:SqlPublishProfilePath=ScriptFile.Publish.xml /p:UpdateDatabase=False

    This uses the Publish Profile but will not deploy the script to the target database.

    hope this helps someone else.


    Ryan Perlman (MSFT)

    Wednesday, May 2, 2012 9:51 AM
  • By the way, I figured this all out and bent it to my will :-)

    So, for the record, my problems were:

    1. Putting /t:Build /t:Publish will fail if you have projects in the solution you're building that don't have a publish target. It's great if you're building just the database project, but not much use if you're building a solution for integration testing.
    2. I have many teams which need to deploy (to their own databases).  They're all working on the same code, but with many branches. They each have a build (or several builds) set up against their own branch, but we don't want to have to set up separate publish.xml files for each build definition.

    I've managed to get it so that I can manipulate the contents of the publish.xml file at compile time, pass /p:DBDeployOnBuild=True as an MSBuild argument, get MSBuild to actually call the publish target while it's doing a regular build for the rest of the solution. I blogged about it here: http://huddledmasses.org/adventures-getting-msbuild-tfs-and-sql-server-data-tools-to-work-together/

    Basically, I'm using the Web Publishing ParameterizeTransformXml to do an XDT transform based on an XML string that we create  based on build properties (which are overridable at build time via MSBuild arguments specified in the TFS build definition).  The result is that we're able to deploy to a database name that matches our TFS Build Definition name without having to call MSBuild twice or use SQLPackager on the build box.

    The catch is that I had to manually add the XML transform stuff to my .sqlproj build/project file in a BeforeBuild step, and add an AfterBuild step to invoke the Publish task ...


    Wednesday, May 2, 2012 6:46 PM
  • Joel, i tried your approach in the huddledmasses link, but i get this error just building the db proj:

    The SqlPublish Profile 'C:\GeicoNewMsi\MSI\Dev\Research\DataMap\src\MsiDataMap.DatabaseDev.publish.xml' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'

    what if any, fields/parameters in the sqlproj file are we do edit?

    Friday, June 1, 2012 5:16 PM
  • Well, that looks different than what I would expect, so let me back up. Presumably that MsiDataMap.DatabaseDev.publish.xml file is the one you're specifying in the BeforePublish target:

      <Target Name="BeforePublish"
              Condition="('$(TargetDatabase)' != '' Or '$(TargetConnectionString)' != '') And Exists($(TransformOutputFile))">
        <PropertyGroup>
          <SqlPublishProfilePath>$(TransformOutputFile)</SqlPublishProfilePath>
        </PropertyGroup>
      </Target>

    But of course, $(TransformOutputFile) has to be set before-hand, in a PropertyGroup so that it will be available in both BeforePublish where the publish file will be generated, and the BeforeBuild target (where it will be used).

    In mine I define it as

    <TransformOutputFile>$(MSBuildProjectDirectory)$(BuildDefinitionName)_$(Configuration).publish.xml</TransformOutputFile>

    Your name looks like you must have changed that configuration ... so the bottom line is that whatever path you're passing to override the SqlPublishProfilePath in the BeforePublish target, you must pass the same path to the ParameterizeTransformXml task for the Destination parameter, so that it will get created:

    <ParameterizeTransformXml
       Source="$(TransformFile)" 
       IsSourceAFile="True"
       Transform="$(TransformPublishXml)" 
       IsTransformAFile="False" 
       Destination="$(TransformOutputFile)" 
       IsDestinationAFile="True" Scope="$(TransformScope)" 
       StackTrace="$(TransformStackTraceEnabled)" 
       SourceRootPath="$(MSBuildProjectDirectory)">

    Tuesday, June 5, 2012 1:01 AM
  • Just wanted to double check on this SSDT tool installation in TFS Server. Do we really need to install the SSDT tool in TFS Server. If so, can anybody explain why? Or can we just install the Administrative Install tool for SSDT as mentioned in this website. Please suggest.

    I’ve been facing different issues while configuring the TFS build for .sqlproj.

    Error #1: SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found

    error MSB4019: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. Database\test.sqlproj : Solution file warning MSB4122: Scanning project dependencies for project "Database\test.sqlproj" failed. The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. Was able to resolve it temporarily by adding this file manually in the TFS Server at the above specified location. Now, am getting the below error.

    Error #2: The attribute "Returns" in element <target> is unrecognized

    [Any CPU/Release] C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(164,35): error MSB4066: The attribute "Returns" in element <target> is unrecognized. C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(164,35): error MSB4066: The attribute "Returns" in element <target> is unrecognized. Database\test.sqlproj : Solution file warning MSB4122: Scanning project dependencies for project "Database\test.sqlproj" failed. The attribute "Returns" in element <target> is unrecognized. C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets

    My configuration is: VS 2010 and TFS 2008.

    Appreciate in advance for any help.


    Thanks, Pravek


    • Edited by pravek Thursday, July 12, 2012 5:59 AM removed unncessary tags
    Thursday, July 12, 2012 5:56 AM
  • Yes pravek, it's enough to install the DACFramework and SSDTBuildUtilities as outlined in that article.
    Friday, July 13, 2012 12:47 AM
  • Thanks Joel for quick reply. Just wanted to check whether I need to install these components in Build machine (or) TFS Server?

    Per this link, we need to install VS 2010 as well in Build Machine. Can you please suggest on this?

    FYI, my environment is similar to the Single machine system (Stand alone) as illustrated in this site.

    


    Thanks, Pravek

    Monday, July 16, 2012 1:57 PM
  • No, that's wrong, you don't need Visual Studio for this. The SSDTBuildUtilities has the targets file.  You might need Visual Studio for other things, but not that. ;)
    Tuesday, July 17, 2012 4:22 AM
  • Adding a build event for the .sqlproj with only this argument :

    /t:Build;Publish /p:SqlPublishProfilePath=[mypublishprofilefilename].xml

    worked perfectly fine! Thank you so much for sharing your idea!


    • Edited by CarlVerret Wednesday, January 11, 2017 3:53 PM
    Wednesday, January 11, 2017 3:53 PM