Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

February 7, 2014

Deploying a DACPAC with SqlPackage

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

SqlPackage is a command line utility that automates DACPAC operations, including publishing a DACPAC to a target database. On my system, I found SqlPackage.exe in: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin.

SqlPackage has many parameters, properties, and variables you can use to perform DACPAC operations. See this MSDN link for details.

The benefits of using SqlPackage are:

  • Its straight-forward and the API is clear.
  • You can include it in a batch file.
  • You can use it to automate your deployment process.

Here are the basic steps for deploying a DACPAC using SqlPackage from the command line:

  • Open a command prompt (cmd.exe).
  • Execute SqlPackage.exe with the desired parameters.

For example: to deploy a DACPAC using a publishing profile, the command would look like this:

“<Path>\SqlPackage.exe”
/Action:Publish
/SourceFile:“<Path>\ACM.Database.dacpac”
/Profile:”<Path>\ACM.Database.publish.xml"

Where <Path> is the appropriate path to your files.

Executing the command displays status messages as it performs the publish operation as shown in the screen shot below.

image

As another example: to deploy a DACPAC by specifying the target database information instead of using a publishing profile, the command would look like this:

“<Path>\SqlPackage.exe”
/Action:Publish
/SourceFile:“<Path>\ACM.Database.dacpac”
/TargetDatabaseName:TestACM
/TargetServerName:".\sqlExpress"

Where <Path> is the appropriate path to your files.

The result is shown below.

image

Use SqlPackage to deploy the DACPAC anytime you want to perform the deployment using the Command window or within a script.

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.

7 Comments

  1.   ABZ — June 7, 2014 @ 5:21 am    Reply

    I’ve more than one Dacpac file fore my database
    one for each schema.
    how can I deploy them by sqlpackage.exe?

    thanks

  2.   DeborahK — June 16, 2014 @ 5:03 pm    Reply

    Hi ABZ –
    You could build a batch file that contained one SQL Package line for each Dacpac file.
    Hope this helps!

  3.   Claire — August 12, 2015 @ 8:23 am    Reply

    Hey,
    I’m having issues with SqlPackage not updating the DacPac version in the database (eg. SELECT type_version, instance_name FROM msdb.dbo.sysdac_instances;).
    If you’ve got any ideas I’d be most grateful and appears to be applying changes fine, just not updating the type_version as I’m expecting.
    Thanks in advance

    •   Claire — August 12, 2015 @ 8:28 am    Reply

      Should add it does update the version fine if I publish using Visual Studio… odd one.

      •   deborahk — August 12, 2015 @ 11:54 am    Reply

        Yes, that is an odd one. I hadn’t heard of this issue before. Have you tried posting to StackOverflow to see if anyone else has run into this?

  4.   richardhig — August 17, 2015 @ 10:10 am    Reply

    We’ve had lots of problems using SSDT, DACPAC and SQLPackage to deploy. There is no way to back up data to a “backup table” within the DB if the table doesn’t exist in the base model. Given this there is no way to automate handling of destructive changes where you need to create a backup of a table, the DACPAC deletes the table (as long as there’s no data in it), then creates a new table with the desired changes, then you have to reload the table from the backup. Unless we include backup tables for all our tables in the base model in SSDT then a backup created at run time in the DB will be deleted by SQLPackage since it is an object that doesn’t exist in the base model. Now your original data is gone. The only solution we’ve come up with is externally backing up tables with destructive changes prior to execution. This requires manual steps for the DBA to create an external backup, run the SQLPackage/DACPAC, then script restoring the data. There’s no built-in capability for pre and post execution scripts that we can find, though MS said at VSLive that there is. If there is it isn’t documented. And even pre/post scripts wouldn’t fix this since they run in the context of the DB. They can’t backup/restore using anything other than the DB.

    Also, it seems to be very difficult to exclude security from promotion of models using DACPACs. We’ve run in to many instances where despite specifying all the correct (we think) options SQLPackage has tried to impose the security of the Development DB from which the DACPAC was created on the Production DB. That breaks everything.

    We may move to a 3rd party solution because of these issues.

RSS feed for comments on this post. TrackBack URI

Leave a comment

*

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