working with MSBuild in order to build and deploy a database project

Question

I managed to build a database project and dumb the output inside a folder with a build script which integrates with MSBuild. Let's say I also need to deploy the that generated sql file to a server. Which options do I have here with MSBuild?

Let's say we can do that, then Inside my build script, can it determine if the deployment fails or not? Any sample on this would be nice.

---

Tugberk Ugurlu

Answer 1

How does your build script "integrate" with MSBuild?  Is there a reason you don't want to use the "Publish" target?  Also, you may find SqlPackage.exe a useful commandline tool.  Again, I'm unsure of your scenario so I'm not sure what the best course for you is. 

Patrick

Answer 2

Hi Patrick,

Thanks for the quick response. Here is the msbuild xml file:

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
         ToolsVersion="4.0"
         DefaultTargets="Compile">
  
  <PropertyGroup>
    <!--We are putting condition here.
        This means that this is only going to be set if this condition true. -->
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  </PropertyGroup>

  <ItemGroup>
    <OutputDir Include=".\Output" />
    <MainSolutionfile Include=".\MvcBloggy.sln" />
    <DatabaseSolutionfile Include=".\MvcBloggy.SQLServer.sln" />
    <DBScriptOutputDir Include=".\Output\SQLScript" />
  </ItemGroup>

  <!--Clean the OutputDir dir.
      This will be the dir we will use to put all of our builds's bits and pieces -->
  <Target Name="Clean">
    <RemoveDir Directories="@(OutputDir)" />
  </Target>
  
  <!--This is the target which will create the dir which we have deleted.
      Also we are telling it that it depends on Clean target.
      So, this will start after the Clean target is finished.-->
  <Target Name="Init" DependsOnTargets="Clean">
    <MakeDir Directories="@(OutputDir)" />
  </Target>
  
  <Target Name="Compile" DependsOnTargets="Init">
    <MSBuild Projects="@(MainSolutionfile)" Targets="Rebuild" Properties="OutDir=%(OutputDir.FullPath);Configuration=$(Configuration)" />
    <MSBuild Projects="@(DatabaseSolutionfile)" Targets="Rebuild" Properties="OutDir=%(DBScriptOutputDir.FullPath);Configuration=$(Configuration)" />
  </Target>

</Project>

and this is the build.ps1 script which fires this off:

sal msbuild "c:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe"
msbuild .\MvcBloggy.msbuild /target:compile /property:Configuration=Release

You may see the full project: https://github.com/tugberkugurlu/MvcBloggy/tree/develop

I can only build the file so far, couldn't figure out how deploy works.

---

Tugberk Ugurlu

Answer 3

Try executing your project on the commandline with /pp and then /v:diag.  Make sure to redirect output to a file.  Like this...

msbuild /t:build /pp MvcBloggy.sqlproj > ppoutput.txt

msbuild /t:build /v:diag MvcBloggy.sqlproj > diagoutput.txt

The pp output is the intermediate msbuild file which the msbuild.exe process executes.  You can use it to see all the .targets files which get included when you build a database project system.

The /v:diag output produces tons of diagnostic output.

From these outputs you should be able to determine a successful path.  I'd try the following:

1. Launch Visual Studio, right click on the root project node and select "Publish".  Set the desired settings and save the profile to a file.

2. Now use MSBuild and the profile to publish.

msbuild /t:Publish /p:SqlPublishProfilePath="mypublishfile.publish.xml" MvcBloggy.sqlproj

The following post by Gert might help you out as well.

http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/ 

Answer 4

You and others may also find a blog post I wrote helpful as well:

Using MSBuild to publish a VS 2012 SSDT .sqlproj database project the same way as a VS 2010 .dbproj database project (using command line arguments to specify the database to publish to)

---

- Dan - "Can't never could do anything"

Answer 5

Nice, thanks for sharing!

---

-GertD @ www.sqlproj.com