3

I have a VS 2012 solution that includes both an SSDT project (sqlproj) and an MVC Web Application. I've setup numerous profiles for deploying the web app and would now like to use dbdacfx (or something else?) to deploy the dacpac produced by the SSDT project. I've tested the dacpac itself and it works flawlessly. However, figuring out how to integrate the two is proving elusive. Every example I've seen for deploying incremental database changes with Web Deployment seems to want an instance of the database - how do I get it to use my existing dacpac instead?

Hopefully I'm asking the right question :)

Thanks!

sp00kyDude
  • 33
  • 6
  • Have you looked into setting up Publish profiles and using SQLPackage to push the incremental changes? – Peter Schott Sep 25 '13 at 22:50
  • I've setup several profiles in both the web application and the sql project, and I can publish all of them using the 'Publish' option in VS for each project. As far as SQLPackage, do you mean calling it from the command line or somehow making the publish profile in the web app use SQLPackage? Sorry, this is a bit new to me. – sp00kyDude Sep 26 '13 at 12:13
  • I was referring to calling SQLPackage from the command line. I'm not quite sure what's going on behind the scenes with your web app deploying code so am not sure if that's a viable option or not. – Peter Schott Sep 26 '13 at 17:33
  • Ended up having to write a powershell script to accommodate the db install using msdeploy and dbdacfx. I could add the dacpac to the publish profile via the UI or directly in the .pubxml and it would get copied to the target machine. However, the dbdacfx provider simply couldn't find the file and would get an error when executing. It just doesn't seem that web deploy is meant to install an arbitrary dacpac. I spent a couple of weeks googling and found very little of any use. I'm leaving this unanswered in the hope that somebody sees it and has a better answer. – sp00kyDude Oct 16 '13 at 15:13
  • I haven't tried to use the dacfx code, but maybe something like Procmon could help you figure out where it's trying to look for the dacpac file. It can be a lot of information to investigate, but I've found those calls to missing folders/files before by using it. http://live.sysinternals.com/ – Peter Schott Oct 20 '13 at 01:40

1 Answers1

1

Assuming you've already got a dacpac file that you want to deploy, you can use SQLPackage.exe to deploy it. Using PowerShell:

& "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /sf:"MyProject.Database.dacpac" /a:publish /TargetConnectionString:$DBConnectionString /p:VerifyDeployment=true /p:CreateNewDatabase=$CreateNewDatabase | Out-Default

This assumes the script is parallel with your dacpac file, and the following variables are defined:

  • $DBConnectionString - Database connection string.
  • $CreateNewDatabase - Whether to create a new database if it does not exist.

There are more options you can pass in: http://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx

Justin Caldicott
  • 739
  • 8
  • 22