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:
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.
