Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

February 10, 2014

Deploying a DACPAC with PowerShell

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 Windows PowerShell.

Windows PowerShell is a task automation and configuration management framework that aids in performing Windows administrative tasks.

Here is a script that will deploy a DACPAC.

add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

$dacService = new-object Microsoft.SqlServer.Dac.DacServices "server=.\sqlexpress"

$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load("<Path> \ACM.Database.dacpac")

$dacService.deploy($dp, "TestACM", "True")

image

The first line adds the Dac DLL to the PowerShell session. Add-Type is a Utility Cmdlet that adds any .NET Framework type to a PowerShell session. Change the directory as appropriate for your system.

The second line creates an instance of the DacServices object and defines the SQL Server instance for the connection. In this example, we are using SqlExpress.

The third line loads the DACPAC. Be sure to change <Path> to the path of your DACPAC and that it is all on one line.

The last line performs the deployment. The first argument is the loaded DACPAC. The second argument is the database that is the target of the deployment. The third argument is whether to allow update of an existing schema. This is "True" because we want to allow this script to upgrade the TestACM database if it already exists.

For more information on using PowerShell with DACPACs, see this blog post.

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.

2 Comments

  1.   David Howell — November 16, 2015 @ 4:29 pm    Reply

    Hi Deborah,
    Thank you for sharing this information, it’s very clear and helpful.

    I am working on a project that has multiple database and CLR DLL references, and these end up in the dacpac as hard-coded absolute paths on my C drive.
    I want to deploy the dacpac from a remote location using either DacFx or SQLPackage. Copying all of the dependent dacpacs and DLLs to the remote location is easy enough, but it seems I also have to unzip the dacpac, and then modify the model.xml file to reflect the new paths to dependencies.
    Do you know another way around this? Is there a way to get a dacpac to store relative paths?

    •   deborahk — July 29, 2016 @ 7:12 am    Reply

      I don’t know of any way to handle relative paths, but I have not worked with dacpac for well over 2 years, so don’t know if anything has been changed/improved. Consider posting a question to stackoverflow.com. The experts there are much more up to date with these technologies.

Trackbacks/Pingbacks

  1. Architecting Innovation Blog | How to Setup Continuous Deployment for a SQL Server Database using Git, PowerShell, SSDT and TeamCity
  2. How to Setup Continuous Deployment for a SQL Server Database using Git, PowerShell, SSDT and TeamCity - Architecting Innovation Blog

RSS feed for comments on this post. TrackBack URI

Leave a comment

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