Using Microsoft.Build.Evaluation to publish a database project (.sqlproj)

Question

I need to be able to publish an SSDT project programmatically. I am looking at using Microsoft.Build to do so but can not find any documentation. It seems pretty simple to create the .dacpac, but how would I either publish to an existing database or at the very least to a .sql file. The idea is to have it do what it does when I right click on the project and select publish. It should compare with a selected database and generate an upgrade script.

This is what I have so far to create the .dacpac:

partial class DBDeploy
{
  Project project;


  internal void publishChanges()
  {
     Console.WriteLine("Building project " + ProjectPath);
     Stopwatch sw = new Stopwatch();
     sw.Start();

     project = ProjectCollection.GlobalProjectCollection.LoadProject(ProjectPath);
     project.Build();
     //at this point the .dacpac is built and put in the debug folder for the project

     sw.Stop();
     Console.WriteLine("Project build Complete.  Total time: {0}", sw.Elapsed.ToString());

  }
}

Essentially I am trying to do what this MSBuild Example shows but in code.

Sorry that this is all I have. The doecumentation on the Build classes is very poor. Any help would be appreciated.

Thanks.

score 20 · Answer 1 · answered Aug 13 '12 at 12:03

I had to do something similar to this because VSDBCMD which we previously used does not deploy to SQL Server 2012 and we needed to support it. What I found was the Microsoft.SqlServer.Dac assembly which seems to come as part of the SQL Server data tools (http://msdn.microsoft.com/en-us/data/tools.aspx)

When you run this on the client machine you will need the full version of the .NET 4 framework and the SQL CLR types and SQL T-SQL ScriptDOM pack found here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

Code below is from a mockup I made for testing the new deployment method and deploys a given .dacpac file

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Dac;
    using System.IO;

    namespace ConsoleApplication3
    {
        class Program
        {
            private static TextWriter output = new StreamWriter("output.txt", false);
            static void Main(string[] args)
            {

                Console.Write("Connection String:");
                //Class responsible for the deployment. (Connection string supplied by console input for now)
                DacServices dbServices = new DacServices(Console.ReadLine());

                //Wire up events for Deploy messages and for task progress (For less verbose output, don't subscribe to Message Event (handy for debugging perhaps?)
                dbServices.Message += new EventHandler<DacMessageEventArgs>(dbServices_Message);
                dbServices.ProgressChanged += new EventHandler<DacProgressEventArgs>(dbServices_ProgressChanged);


                //This Snapshot should be created by our build process using MSDeploy
                Console.WriteLine("Snapshot Path:");

                DacPackage dbPackage = DacPackage.Load(Console.ReadLine());




                DacDeployOptions dbDeployOptions = new DacDeployOptions();
                //Cut out a lot of options here for configuring deployment, but are all part of DacDeployOptions
                dbDeployOptions.SqlCommandVariableValues.Add("debug", "false");


                dbServices.Deploy(dbPackage, "trunk", true, dbDeployOptions);
                output.Close();

            }

            static void dbServices_Message(object sender, DacMessageEventArgs e)
            {
                output.WriteLine("DAC Message: {0}", e.Message);
            }

            static void dbServices_ProgressChanged(object sender, DacProgressEventArgs e)
            {
                output.WriteLine(e.Status + ": " + e.Message);
            }
        }
    }

This seems to work on all versions of SQL Server from 2005 and up. There is a similar set of objects available in Microsoft.SqlServer.Management.Dac, however I believe this is in the previous version of DACFx and is not included in the latest version. So use the latest version if you can.

Brilliant, using the DACPAC assemblies allow me to take full control of the deployment. Also useful for integration tests! — Raffaeu, Oct 21 '13 at 12:32
Finally, a sensible answer! Thanks - Just want to add that you need to copy/reference the `sqlserver.dac.dll` from `Microsoft Visual Studio ??.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120` - Works really well, exactly what I needed, so simple and fast. This should be accepted! — Piotr Kula, Feb 27 '15 at 16:15
Any experience with setting the flag "upgradeExisting" to false? I get an exception telling me "Cannot deploy to existing database when upgrading has been disabled". But I want DacServices to remove the database first. It doesn't work, even if I add options with CreateNewDatabase = true — Peter, Jun 26 '15 at 09:15
@Peter - there is an overload for upgradeExisting: services.Deploy(package, connectionString, upgradeExisting: true); — Dave Mateer, Apr 25 '16 at 05:23

Keith Robertson · Answer 2 · 2012-06-07T15:59:37.397

3

We need a way tell msbuild how and where to publish. Open your project in Visual Studio and begin to Publish it. Enter all needed info in the dialog, including your DB connection info and any custom SQLCMD variable values. Save Profile As... to a file, e.g. Northwind.publish.xml. (You may then Cancel.) Now we can use this and the project file to build and publish:

// Create a logger.
FileLogger logger = new FileLogger();
logger.Parameters = @"logfile=Northwind.msbuild.log";
// Set up properties.
var projects = ProjectCollection.GlobalProjectCollection;
projects.SetGlobalProperty("Configuration", "Debug");
projects.SetGlobalProperty("SqlPublishProfilePath", @"Northwind.publish.xml");
// Load and build project.
var dbProject = ProjectCollection.GlobalProjectCollection.LoadProject(@"Northwind.sqlproj");
dbProject.Build(new[]{"Build", "Publish"}, new[]{logger});

This can take awhile and may appear to get stuck. Be patient. :)

edited Jun 07 '12 at 15:59

answered Jun 07 '12 at 14:04

Keith Robertson

791
7
13

1

I needed to reference both the `Microsoft.Build` and `Microsoft.Build.Framework` assemblies for this code to work. – Sam Aug 29 '13 at 01:01
Make sure to check the return value of `Build` to see if it was successful or not. – Sam Aug 29 '13 at 01:43
To monitor build events, use a `ConfigurableForwardingLogger` and set `BuildEventRedirector` to a custom `IEventRedirector`. You can check for errors by checking if `buildEvent` is a `BuildErrorEventArgs`. – Sam Aug 29 '13 at 01:45
Its ok if you want to publish to one DB the whole time, but the DAC seems to allow me to target any server, or databases, so I can create and drop databases as needed, based on the DACPAC. – Piotr Kula Feb 27 '15 at 13:53

Paul · Answer 3 · 2015-03-11T23:27:45.080

1

You should use SqlPackage.exe to publish your dacpac.

SqlPackage.exe 
  /Action:Publish 
  /SourceFile:C:/file.dacpac 
  /TargetConnectionString:[Connection string]

Also instead of passing too many parameters you could save your settings into DAC Publish Profile (this can be done from visual studio)

edited Mar 11 '15 at 23:27

answered Jun 11 '12 at 18:48

Paul

1,879
1
23
44

1

`should` ... should be `could` :) – Piotr Kula Feb 27 '15 at 13:52

Todd Menier · Answer 4 · 2016-07-08T18:32:19.940

I wanted to build and publish a database based on a sqlproj file and log helpful information to console. Here's what I arrived at:

using Microsoft.Build.Framework;
using Microsoft.Build.Execution;

public void UpdateSchema() {
    var props = new Dictionary<string, string> {
        { "UpdateDatabase", "True" },
        { "PublishScriptFileName", "schema-update.sql" },
        { "SqlPublishProfilePath", "path/to/publish.xml") }
    };

    var projPath = "path/to/database.sqlproj";

    var result = BuildManager.DefaultBuildManager.Build(
        new BuildParameters { Loggers = new[] { new ConsoleLogger() } },
        new BuildRequestData(new ProjectInstance(projPath, props, null), new[] { "Publish" }));

    if (result.OverallResult == BuildResultCode.Success) {
        Console.WriteLine("Schema update succeeded!");
    }
    else {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine("Schema update failed!");
        Console.ResetColor();
    }
}

private class ConsoleLogger : ILogger
{
    public void Initialize(IEventSource eventSource) {
        eventSource.ErrorRaised += (sender, e) => {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine(e.Message);
            Console.ResetColor();
        };
        eventSource.MessageRaised += (sender, e) => {
            if (e.Importance != MessageImportance.Low)
                Console.WriteLine(e.Message);
        };
    }
    public void Shutdown() { }
    public LoggerVerbosity Verbosity { get; set; }
    public string Parameters { get; set; }
}

This is for .NET 4 and above. Be sure and include assembly references to Microsoft.Build and Microsoft.Build.Framework.

Using Microsoft.Build.Evaluation to publish a database project (.sqlproj)

4 Answers4

Linked