Deploying Dacpacs with DacFx and PowerShell

When it comes to managing Dacpacs with automation (eg either by deploying or extracting Dacpacs from a database, amongst others), you have a couple of options available to you.

SQLPackage

SQLPackage is a command line utility that is shipped with SSDT, and it’s main focus is to manage Dacpacs. It’s certainly an in-depth tool: This is the MSDN site for all the parameters that are available for SqlPackage.exe.

I’ve written before about how to put this into a PowerShell module, however it’s basically a wrapper around the command line. Not that there is anything wrong with this approach, however if you want a more programmatic method then you may want to use DacFx.

DacFx

DacFx, or to give it it’s full title, the Data-tier Application Framework “is a component which provides application lifecycle services for database development and management for Microsoft SQL Server and Microsoft Azure SQL Databases“. Essentially, it is another method we can use to manage our Dacpacs. However instead of using the external process SQLPackage and initiating it via cmdline you can use C# or PowerShell to manage Dacpacs. In fact, SQLPackage uses the “Microsoft.SqlServer.Dac.dll” itself. You can verify this by going and deleting the dll and trying to run sqlpackage via command line…. or you can just take my word for it.

I’ve provided a sample below as to how you can deploy a dacpac using DacServices. The PowerShell script below takes 4 arguments (database server, database name, dacpac and profile) and deploys the changes to make to the database. It also creates a script of the database changes for reference. If you have multiple dacpacs, it would not take too much effort to loop through the dacpacs and find their relative profiles, if any exist. Note that the profile argument is entirely optional, and you can build up your own profile options from within the PowerShell script. The options I’ve added here are the “least risk” options; that is, no objects/permissions or data will be lost when deploying. It’s probably these three options that people are most afraid of when automating database deployments.


param([string]$targetConnectionString, [string]$Dacpac, [string]$targetDatabaseName, [string]$Profile)

$dacfxPath = 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll'

$logs = "C:\DacpacReport"

#create log path
New-Item $logs -ItemType directory -force | out-null;

# Load the DAC assembly
Write-Verbose 'Testing if DACfx was installed...'
$validate = Test-Path $dacfxPath
if (!$dacfxPath){
throw 'No usable version of Dac Fx found.'
}
Write-Verbose -Verbose 'DacFX found, attempting to load DAC assembly...'
Add-Type -Path $dacfxPath
Write-Verbose -Verbose 'Loaded DAC assembly.'

# Load DacPackage
$dacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($Dacpac)

# Load DacProfile
if ($profile -ne " ") {
$dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($Profile)
Write-Host ('Loaded publish profile ''{0}''.' -f $Profile)
} else {
$dacProfile = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{
'BlockOnPossibleDataLoss' = $true;
'DropObjectsNotInSource' = $false;
'ScriptDatabaseOptions' = $true;
'IgnorePermissions' = $true;
'IgnoreRoleMembership' = $true
}
}

# Setup DacServices
$server = "server=$targetConnectionString"
$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $server

# Deploy package
try {
Write-Host 'Starting Dacpac deployment...'
$dacServices.GenerateDeployScript($dacPackage,$targetDatabaseName, $dacProfile.DeployOptions) | Out-File "$logs\$targetDatabaseName.sql"
$dacServices.Deploy($dacPackage, $targetDatabaseName, $true, $dacProfile.DeployOptions, $null)
Write-Host 'Deployment succeeded!'
} catch [Microsoft.SqlServer.Dac.DacServicesException] {
throw ('Deployment failed: ''{0}'' Reason: ''{1}''' -f $_.Exception.Message, $_.Exception.InnerException.Message)
}

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s