Publishing DACPACs with Powershell

Question

Hi,

Does anyone know of a good blog to step through publishing SQL 2012 dacpacs with powershell? I've tried running the code from here: http://msdn.microsoft.com/en-us/library/ee210569.aspx#DeployDACPowerShell and get all kinds of errors so would like a worked example. And if it included how to use publish profiles that would be even better!

A lot of the other things that searches bring up seem to be for older versions so not even sure I should be using them!

Cheers

Leyton

Answer 1

Hi, that example appears to use the old Dac v2.0 API. You need to use the Dac v3.0 API, which is online at http://msdn.microsoft.com/en-us/library/hh753459.aspx. The DacServices class is the main entry point for requesting Dac operations.

For a C# example please see this post: http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/ede68ac7-33b2-4650-a94c-8a1ba892e471/. The same principles apply via Powershell since you're just calling into a C# API.

Answer 2

Hi,

Thanks for the reply very handy. With some (more powershell specific) help from http://www.sqlskills.com/blogs/bobb/using-powershell-with-dac-3-0/ I have now got this script which has got the basics in place:

#Register the DLL we need
Add-Type -Path "${env:ProgramFiles(x86)}\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll" 

#Read a publish profile XML to get the deployment options
$dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load("C:\temp\testdeploy\BasicDB.publish.xml")

#Use the connect string from the profile to initiate the service
$dacService = New-Object Microsoft.SqlServer.dac.dacservices ($dacProfile.TargetConnectionString)
 
#Load the dacpac
$dacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load("C:\temp\testdeploy\BasicDB.dacpac")

#Publish or generate the script (uncomment the one you want)

#$dacService.deploy($dacPackage, $dacProfile.TargetDatabaseName, $true, $dacProfile.DeployOptions)
#$dacService.GenerateDeployScript($dacPackage, $dacProfile.TargetDatabaseName, $dacProfile.DeployOptions)

Cheers

Leyton

Answer 3

Looks great Leyton, I'll switch my powershell that currently calls sqlpackage.exe to use this when I get a chance.

One issue that I currently have is that ideally I'd like to be able to have the compare done once and a report/script and publish done all off of the same comparison but currently with sqlpackage you have to call it 3 times and each time it is doing a new comparison (much more load on the server and takes longer with a complex project).

Is there any way to get closer to this using daxfx cmdlets?

Answer 4

Hi Brett,

Not that I'm aware of. There are distinct methods for producing scripts, drift reports and doing the publish (much like calling sqlpackage with the different actions). But if you've produced a deployment script then that can be run via SSMS / powershell/ sqlcmd so in that case you'd only need the comparison once?

Although I do see what you mean in terms of being able to do the report, check it, then just run the publish if happy with it.

Cheers

Leyton