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.

2 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!

RSS feed for comments on this post. TrackBack URI

Leave a comment

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