How do I publish a SSDT Database from code

Question

We have SQLServer01.Publish.xml when I double click this file and publish, it publishes a database to sqlServer01.

I wanted to ask can we publish this profile from code somehow ?

Whereto

call msbuild like this blog.danskingdom.com/… – Knick

Answer 1

SSDT seems to like DacPac for this kind of thing. There is a DacServices utility class in Microsoft.SqlServer.Dac. I think this will require SSDT to be installed on the machine you plan on running this code.

public class DacPacUtility
{
    public void DeployDacPac( string connString, string dacpacPath, string targetDbName )
    {
        var dbServices = new DacServices( connString );

        var dbPackage = DacPackage.Load( new FileStream( dacpacPath, FileMode.Open, FileAccess.Read ), DacSchemaModelStorageType.Memory, FileAccess.Read );

        var dbDeployOptions = new DacDeployOptions()
        {
            SqlCommandVariableValues =
            {
                new KeyValuePair< string, string >( "debug", "false" )
            },
            CreateNewDatabase = true,
            BlockOnPossibleDataLoss = false,
            BlockWhenDriftDetected = false
        };

        dbServices.Deploy( dbPackage, targetDbName, upgradeExisting : true, options : dbDeployOptions );
    }
}

Bonus: you can use the Microsoft.Build.Evaluation.Project namespace to new up a Project object and build it locally for integration testing using the resulting built dacpac to initialize a test.

[SetUpFixture]
public class TestSetup
{
    [SetUp]
    public void SetUpTests()
    {
        var projectPath = @"C:SomeDirectory";
        var project = new Project( projectPath );
        project.Build();
        ProjectCollection.GlobalProjectCollection.UnloadProject( project );

        var dacPac = new DacPacUtility();
        var connString = "Data Source=(localdb)\ProjectsV12;Initial Catalog=Tests;Integrated Security=True";
        var dacPacPath = projectPath + "..\bin\projectName.dacpac";
        dacPac.DeployDacPac(connString, dacPacPath, "Tests");
     }
     [TearDown]
     public void TearDownTests()
     {
       // TODO: delete db or run other cleanup scripts
     }
 }

References:

Deborah's Developer MindScape: Deploying A DACPAC

Latest SSDT

SSDT for VS 2010

Where to get Microsoft.SqlServer.Dac and so DacService

There are a couple of ways to get the Microsoft.SqlServer.Dac library, either:

• Add the Nuget package
• Or install the SSDT tools on your dev machine, and then reference the DLL.

Corset

thanks for you reply, but I cant find DACServices, is it because we are using 2008R2 and vs2010 ? – Whereto

Warning: While most of this is great, I recommend not using new FileStream to load the dacpac; If you have a dependancy on another dacpac, for example master.dacpac, and that the SDK is not deployed on the machine running that code, it won't be able to find it in the same directory (what the engine tries to do) since there is no directory, only a stream, from the point of view of the engine. Instead, just pass the path of the file, and let the engine handle it. – Einberger

Thanks @Tipx! Your tip saved me a lot of trouble. For the reference finding to work you also need "IncludeCompositeObjects = true" in the DacDeployOptions. @Corset could you please update the answer as per these comments? – Naji