Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

February 11, 2014

Deploying a DACPAC with DacFx API

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using the DacFx API.

The DacFx API is a set of classes that you can use in your code to perform operations on a DACPAC. This allows you to write your own DACPAC utility application or include DACPAC functionality in any application.

DacFx API Version 3.0 is defined in Microsoft.SqlServer.Dac, which is a different DLL than prior DacFx API versions. Along with a new DLL, the functionality in Version 3.0 changed significantly from prior versions. The information in this post is for Version 3.0 and won’t work with prior DacFx API versions.

NOTE: DacFx 3.0 can work with DACPACs from older versions, but only generate Dac 3.0 formats.

Why would you want to write your own code to process a DACPAC when you can deploy a DACPAC with existing tools?

  • You can completely control the target connection and database(s) used, the DACPAC that is used, and the deployment options.
  • You can repeat the processing of the DACPAC for multiple databases.

For example, say you have a set of testers, each with their own copy of the database so they can better verify their results. You can store their connections in a table or configuration file. Then write a DACPAC utility application that loops through each connection and deploys the DACPAC to each tester’s database in one operation.

The code below is in C#, but this technique works in VB.NET as well.

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Dac;

namespace DacpacUtility
{
    public class DacpacService
    {
        public List<string> MessageList { get; set; }

        public DacpacService()
        {
            MessageList = new List<string>();
        }

        public bool ProcessDacPac(string connectionString,
                                    string databaseName,
                                    string dacpacName)
        {
            bool success = true;

            MessageList.Add("*** Start of processing for " +
                            
databaseName);

            var dacOptions = new DacDeployOptions();
            dacOptions.BlockOnPossibleDataLoss = false;

            var dacServiceInstance = new DacServices(connectionString);
            dacServiceInstance.ProgressChanged +=
              new EventHandler<DacProgressEventArgs>((s, e) =>
                            MessageList.Add(e.Message));
            dacServiceInstance.Message +=
              new EventHandler<DacMessageEventArgs>((s, e) =>
                            MessageList.Add(e.Message.Message));

            try
            {
                using (DacPackage dacpac = DacPackage.Load(dacpacName))
                {
                    dacServiceInstance.Deploy(dacpac, databaseName,
                                            upgradeExisting: true,
                                            options: dacOptions);
                }

            }
            catch (Exception ex)
            {
                success = false;
                MessageList.Add(ex.Message);
            }

            return success;
        }
    }
}

Walking through this code:

  • A MessageList property retains any messages generated by the process. The code using this class can display the contents of this list.
  • The constructor initializes the MessageList.
  • The only method in this class deploys a DACPAC file.
  • The parameters to the method define the appropriate target connection, target database, and DACPAC path and file name.
  • The process kicks off with a starting message in the MessageList.
  • If desired, you can define deployment options. In this example, the only option that is set is the BlockOnPossibleDataLoss.
  • An instance of the DacFx DacServices class is then initialized.
  • Optionally, you can elect to respond to ProgressChanged events. These events are invoked when the state of the operation changes. In this case, any ProgressChanged message are added to the MessageList.
  • Optionally, you can elect to respond to Message events. These events are invoked when an operation reports status updates or errors.
  • Within a Try block, the code loads the DACPAC using the DacFx DacPackage class. The argument is the full path and file name to the DACPAC file.
  • Finally, the DACPAC is deployed using the Deploy method of the DacServices class.

To see how this method is called, here is an automated code test:

using DacpacUtility;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace DacpacUtility.Test
{
    [TestClass()]
    public class DacpacServiceTest
    {
        public TestContext TestContext { get; set; }

        [TestMethod()]
        public void ProcessDacPacTest()
        {
            // Arrange
            var target = new DacpacUtility.DacpacService();

            // Act
            var success = target.ProcessDacPac(
                @"Data Source=.\sqlexpress;Integrated Security=True;",
                 "TestACM",
                @"<path>\ACM.Database.dacpac");

            // Assert
            Assert.AreEqual(true, success);

            // Display the messages
            foreach (var item in target.MessageList)
            {
                TestContext.WriteLine(item);
            }
        }
    }
}

Walking through this code:

  • A TestContext property is defined to write out to the TestContext.
  • The arrange process sets up the instance of our DacpacService class.
  • The act process calls the ProcessDacPac method of our DacpacService class and passes the appropriate arguments. Be sure to replace <path> with the path to your DACPAC.
  • The assert process asserts that the deployment was successful.
  • It then writes out all of the messages.

For this example, the messages appear as follows:

image

Notice that there is some message duplication here. That is because we put both the Messages and the Process Changed text in the list.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

4 Comments

  1.   Vijay — October 15, 2014 @ 3:44 am    Reply

    Hi,

    I am using your code to create database and synchronize database, it is working fine. But when DacPac has issues while creating database objects it is not giving any return value or roll-backing. Anyhow this code is not working properly in debug mode. Could you give me possible ways to achieve those things….

  2.   Paul.K — June 15, 2015 @ 9:14 pm    Reply

    How do I enforce the .Deploy() not to drop tables and columns if they don’t exist in a source database?
    I have tried a deployoptions.DropObjectsNotInSource = false but everything is still gone.

    I am using DacFx May 2015 version.
    thank you.

  3.   Thang — November 22, 2016 @ 4:31 am    Reply

    Hi,

    Can DacFx support to set default data file and log paths when deploying a new database?

    Thanks

    •   deborahk — December 6, 2016 @ 11:25 am    Reply

      Thank you for your post!
      I have not used this for a while and don’t know what (if anything) has changed over the past almost 3 years. Your best bet is to post to stackoverflow.com where you will find someone with more recent experience.

Trackbacks/Pingbacks

  1. SSDT / dacpac | Vincent

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs