Azure DevOps, Scrum, & .NET Software Leadership and Consulting Services

Free course! Predicting the Future, Estimating, and Running Your Projects with Flow Metrics

Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe


Are you a big user of the SQL Server database projects?  You know…what used to be known as ”data dude”?  Did you get way into doing command line deployments of your databases using vsdbcmd.exe?  Well, if you are one of those developers and you’ve upgraded to the latest version of the SQL Server Database Projects, then you might be wondering just where vsdbcmd.exe went. 

Answer: vsdbcmd.exe has been replaced with SqlPackage.exe

There were a lot of updates for the VS2012 version of the SQL Server Database Projects so it makes sense that vsdbcmd would get tweeked, too.  Especially, since the outputs of database projects have completely changed.  (Goodbye, *.dbschema files.  Hello, *.dacpac.)

For this blog post, let’s assume a very simple SQL Server Database Project called Benday.SampleApp.Database (as shown in Figure 1) that, upon “compile”, generates a dacpac file named “Benday.SampleApp.Database.dacpac”.

SNAGHTML12611e2a

Figure 1 – The database project

Thankfully, IMHO, SqlPackage.exe is about a trillion times easier to work with.  If I wanted to deploy this database to an instance of SQL Server named demo2012util, here’s what my command line syntax would be:

SqlPackage /Action:Publish
   /SourceFile:”Benday.SampleApp.Database.dacpac”
   /TargetDatabaseName:BendaySampleAppDb
   /TargetServerName:”demo2012util”

Pretty easy, right?  Set the /Action to Publish.  Specify the /SourceFile and point it to the *.dacpac.  Tell it what to name the database using /TargetDatabaseName.  Give it the /TargetServerName for the SQL Server instance.  Done. 

You can make this even easier if you add a “Publish Database” profile to your project.  In Figure 2, you can see the two profiles that I created – one for the “dev” environment named Benday.SampleApp.Database.dev.publish.xml and one for the “prod” environment named Benday.SampleApp.Database.prod.publish.xml.  These files contain all the configuration values that you’d use to deploy a SQL Server dacpac into a particular environment. 

SNAGHTML126a1bca
Figure 2 – The “Publish Database” profiles in the SQL Server Database Project

Since the profile (*.publish.xml) file has all the config values, when you go to deploy the database from the command line using SqlPackage.exe, you don’t have to supply the /TargetDatabaseName or the /TargetServerName anymore and instead you just provide the path to the dacpac using /SourceFile and then specify the profile using the /Profile argument.

Here’s what that command would look like:

SqlPackage
   /Action:Publish
   /SourceFile:”Benday.SampleApp.Database.dacpac”
   /Profile:”Benday.SampleApp.Database.prod.publish.xml”

At this point, you’re probably sarcastically thinking “WHOOPTEEDOOO!!! I’ve exchanged 4 command line arguments for 3 command line arguments! Think of all the extra free time I’ll have now!  Thanks, Ben!”  If your deployment arguments are as simple as server instance and database name then yah…not that impressive.  But there are a lot more configuration options available to you on the command line and typing/managing all those values will definitely be an annoying and error-prone process.

Just for giggles, try double-clicking on one of those *.publish.xml files.  You should see the Publish Database dialog for that deployment profile as shown in Figure 3.  This dialog lets you set the target database connection and the database name. 

image
Figure 3 – The Publish Database editor dialog

Impressed yet?  Not really?  Ok. Click the Advanced button.  KA-POWWWWWW!  Aw snap!  You’ve just been pwned!  Or whatever the cool kids say in order to indicate that something kinda awesome just happened up.  I’m pretty solidly lame.  Cut me some slack already.  Granddad’s tired.  (I’m 38.  Practically a senior citizen in the development world.)

ANYWAY!  You should now see the Advanced Publish Settings dialog and it should look something like Figure 4.  There are a TON of settings that you can…uhmmm…set for your database deployments. 

image
Figure 4 – The Advanced Publish Settings editor dialog

As these settings get complex, it becomes more and more important that these settings aren’t left up to chance and are under version control just like everything else.  One of the biggest time savers when it comes to deploying code and databases is having a well-known automated way to deploy where all your config settings are scripted out.  If it’s scripted, it’s easy.  If it’s under version control, it’s traceable. 

Create Your First Database Publish Profile

Ok. How do I create this profile?  

Step 1: Right-click the database project and choose Publish (Figure 5)

SNAGHTML1280ee36
Figure 5 – Right-click the database project and choose publish from the context menu

Step 2: Populate the values for your settings and click the Create button (Figure 6)

image
Figure 6 – Enter your settings and click Create Profile

After you click Create Profile, you should see a new *.publish.xml file in your project (Figure 7).

SNAGHTML12840f8b
Figure 7 – The new deployment profile

Step 3: This step is optional but I think it’s handy.  Change the “Copy to Output Directory” setting on the new *.publish.xml file to “Copy Always” (Figure 8).  This ensures that when the project is compiled (via the Build command) that the publish profile is copied to the bin directory along with the *.dacpac file.  This makes scripting the deploy using SqlPackage.exe easier when you’re deploying from a developer machine and MUCH easier if you’re deploying from an automated build. 

SNAGHTML1285e032
Figure 8 – Set the Copy to Output Directory to Copy Always

 

I hope this is helpful and/or useful.

-Ben

 

— Want to streamline your database and binary deployment process?  Looking for suggestions on how to make all this run smoothly from Team Foundation Server’s automated build?  Thinking it might be nice to get some training on how to put your insanely complex database under source control using SQL Server Database Projects?  Drop us a line at info@benday.com.

SUBSCRIBE TO THE BLOG


31 responses to “Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe”

  1. Robert Vincent Avatar
    Robert Vincent

    Ben,

    Have you found the new VS2012 DB projects improved enough to stop using RedGate’s SQL Source Control?

    1. Ben Day Avatar

      I’ve never gotten hooked on the Red Gate stuff but it always looks pretty good. The VS2012 projects are very solid and a lot less buggy than the previous release.

      -Ben

  2. max Avatar
    max

    Is there any support for migrations? In case my dev environment is on version 2.0.1.146, my first QA environment is on 1.5.6.18, my second QA is 1.6.3.128, and production is 1.0.7.5?

    1. Ben Day Avatar

      If by “migration” you mean applying an incremental schema update over an existing database while preserving the data, the answer is yes.

      -Ben

      1. max Avatar
        max

        Thanks, yep, that’s what I meant. 🙂 How does it know which upgrades to run?

        1. Ben Day Avatar

          Magic. 🙂

          The dacpac has the schema definition version that you want to update to. When you run SqlPackage /Action:Update, it internally generates a diff and then generates and applies the appropriate updates to make the target database schema the same as the dacpac version.

          So…yah…magic. 🙂

          -Ben

          1. max Avatar
            max

            Thanks! (It does sound fancy 🙂 )

  3. Mark Wojciechowicz Avatar
    Mark Wojciechowicz

    Hi Ben,
    How do you version databases when you publish them? By version, I mean store the version number and time that it was applied in a table.

    Do you use the dacpac version number or some other manual method for versioning? i.e. in the past, we would manually increment the version number in a post deployment script.

    -Mark

    1. Jamie Avatar

      Hi Mark,
      I’ve been using dacpacs for a while now and I still stick with the latter method that you describe there – manually increment the version number in a post deployment script.
      Actually, more accurately we insert the build number from our CI build – but that’s effectively the same result. Its a number inserted into a table from a post-deployment script.

      In other words, we don’t use the dacpac version number.

      regards
      Jamie

  4. […] you want a bit more info on Publish Profile files and sqlpackage.exe check out Ben Day's blog post Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe Published Wednesday, May 09, 2012 12:07 AM by jamiet Filed under: sql server, SQL Server 2012, […]

  5. Narasimham Avatar
    Narasimham

    I created a build process template as described in http://msdn.microsoft.com/en-us/library/ff805001.aspx and used SqlPackage.exe instead of vsdbcmd.exe. I used the parameters that you specified as arguments in my build. However, my build always fails with the below error.

    Exception Message: File not found: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe (type FileNotFoundException)
    Exception Stack Trace:
    Server stack trace:
    at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()
    at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.InvokeProcessInternal.RunCommand(AsyncState state)
    at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
    at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

    My Build Controller and Build Agent are on two separate servers. Will that be the cause of this issue?

    1. Ben Day Avatar

      Narasimham,

      I think you might need to install Visual Studio 2012 on that agent machine.

      -Ben

      1. Narasimham Avatar
        Narasimham

        Ok. Let me try that. Thanks for the update.

        1. Narasimham Avatar
          Narasimham

          I installed VS 2012 and also rebooted. I still get the same error as before. Am I missing something else.
          Exception Message: File not found: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe (type FileNotFoundException)
          Exception Stack Trace:
          Server stack trace:
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.InvokeProcessInternal.RunCommand(AsyncState state)
          at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
          at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

          Exception rethrown at [0]:
          at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
          at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
          at System.Func`2.EndInvoke(IAsyncResult result)
          at System.Activities.AsyncCodeActivity`1.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
          at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

          Inner Exception Details:

          Exception Message: The system cannot find the file specified (type Win32Exception)
          Exception Stack Trace: at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()

  6. David Turner Avatar

    Ive never liked the automation side of this as I dont like the delta being generated on the fly to production servers. What I have done is created a “Sql Migrations” type thing like Entity Framework migrations while still using the power SqlPackager and SqlCmd.

  7. Eric Aldinger Avatar
    Eric Aldinger

    Thanks so much for this concise answer to a process I was trying to understand. And the tip about Always Copy should not be optional, it is so helpful.

  8. zhi an Avatar
    zhi an

    Is there anyway to stop sqlPackage.exe from running the post-deployment script?

  9. Praneet Avatar

    Can we override the argument supplied by the .xml publish file?
    e.g.: If the .xml publish file applies TargetServerName as “ABC”, can it be overridden to another value (say TargetServerName=”xyz”)applied via command line argument?

  10. Amjid Avatar
    Amjid

    Hi, great blog. It’s been real useful. You say you have been using it for a while. I am thinking of using dacpac for a system I support. We do releases every two weeks. Schema changes on most releases. Have you been using it for many releases on the same database? If so how many releases and have you ever had any issues regarding data loss of incorrect schema changes?

    1. Ben Day Avatar

      Hi Amjid —

      I don’t have any customers who are currently doing incremental deploys from automated builds using dacpacs right now. The number of releases shouldn’t matter because the upgrade path is always from whatever is deployed in the db to the dacpac version. There are options in the update process that will block the upgrade on possible data loss. Personally, I wouldn’t worry too much but I’d *DEFINITELY* test my updates in a staging environment before going to production.

      -Ben

  11. Richard Schaefer Avatar
    Richard Schaefer

    It appears SQLPackage.exe doesn’t support standard long path names. It isn’t in my system path so I ran it from where it was installed and specified the full path/file for the DACPAC and Publish Profile as well as my target connection string. The command returned dozens of errors everywhere there was a blank in one of the parameters. This is my command:

    sqlpackage.exe /a:Publish /op:”c:\temp\” /pr:”C:\Users\rs02130\Documents\Visual Studio 2012\Projects\Database1\Database1\bin\Output\Database1.publish.xml” /sf:”C:\Users\rs02130\Documents\Visual Studio 2012\Projects\Database1\Database1\bin\Output\Database1.dacpac” /tcs:”Data Source=CNU1370MFL\MSSQLSERVER12;Initial Catalog=Database1;Integrated Security=True;”

    Any thoughts? I thought MS was past the whole “short path” thing…

  12. Richard Schaefer Avatar
    Richard Schaefer

    I ran the command from the directory where SQLPackage is installed. Still has blanks due to the TargetConnectionString:

    “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe” /a:Publish /op:”c:\temp\” /pr:”Database1.publish.xml” /sf:”Database1.dacpac” /tcs:”Data Source=CNU1370MFL\MSSQLSERVER12;Initial Catalog=Database1;Integrated Security=True;”

    I get these errors:

    *** Unrecognized command line argument ‘Source=CNU1370MFL\MSSQLSERVER12;Initial’
    .
    *** Unrecognized command line argument ‘Catalog=Database1;Integrated’.
    *** Unrecognized command line argument ‘Security=True;’.

    1. Naeem Avatar
      Naeem

      @Richard, did you find a solution to your problem

  13. Jon K. Avatar
    Jon K.

    Great post. Thanks.

    Have you found a way to use PS and DacServices to deploy when the dacpac requires SQLCMD variables? Our project requires this.

    1. Nirman Doshi Avatar

      You can do that by passing SQL CMD variables in “/v” switch of sqlpackage.exe

      For example:

      sqlpackage.exe /Action:Publish /SourceFile:”C:\…\abcd.dacpac” /v:dbName=”myDatabase” /v:FilePath=”C:\Test”

      In above example, dbName, and FilePath are SQL CMD variables

  14. Nirman Doshi Avatar

    Very nice write up.. and clear explanation.
    I am relatively new to dacpacs, and following is my exact scenario:
    – the database project is having a reference of CLR assembly.
    – TFS build server is used to build the project
    – dacpac is getting created during TFS build process
    – when i try to run sqlpackage.exe to deploy the dacpac in my environment, I get an error saying –
    *** No file was supplied for reference XXXX.XXXX.XXXX.dll; deployment might fail. When C:\Temp\Dacpac_testing\XXX.XXXXX.XXXX.dacpac was created, the
    original referenced file was located C:\BUILDS\1\XXXX\XXXX\SRC\XXXXXXXX\ASSEMBLIES\XXXX.XXXX.XXXX.dll

    How can I create a dacpac to reference the path on deployment environment (and not the one that was build at the time of creating dacpac)

  15. George Walkey Avatar
    George Walkey

    This just came in handy today….Thanks!!!

  16. Juan C. Vega Avatar
    Juan C. Vega

    Hi – I am trying to use the dacpac with Release Management studio Dacpac Deployer (which in essence is using sqlpackage). It works great for deploying to DBs on a server.

    However, I am now trying to deploy to a High Availability Always On SQL Environment where there are 3 SQL boxes and a Listener – my question is this:
    – am I supposed to deploy to the Listener or to each individual box? I have tried both options and I get the same error on both. What I am I doing wrong?

  17. senevic Avatar
    senevic

    Can someone help me with merging data using DACPAC. I have just published the dacpac. We’ve realised that we missed left out some data. We need to insert the data in…. can we re-run and merge?

    Thanks

  18. Ahad Zubair Avatar

    Hi, can anybody guide: I am getting Path/Folder not found error :

    7T14:06:01.7089350Z ##[error]System.Management.Automation.ParameterBindingException: Cannot bind parameter ‘Path’ to the target. Exception setting “Path”: “Cannot find path ‘C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll’ because it does not exist.” —> System.Management.Automation.SetValueInvocationException: Exception setting “Path”: “Cannot find path ‘C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll’ because it does not exist.” —> System.Management.Automation.ItemNotFoundException: Cannot find path ‘C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll’ because it does not exist.

    While deploying my SQL script via DACPAC file using Hosted Agent 2017 inside VSTS 2017. This folder hierarchy is actually exists both on my/source machine and on the target machine then why this is happening?

    Thanks. Regards.

    Ahad Zubair

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.