In PowerShell I’m using Microsoft.SqlServer.Dac.DacServices and Microsoft.SqlServer.Dac.DacDeployOptions to deploy/update a database DACPAC. The problem I am having is finding where to set the SQLCMD Variables the package requires.
# Create a DacServices object, which needs a connection string
$dacsvcs = New-Object Microsoft.SqlServer.Dac.DacServices "server=$sqlserver"
# Load dacpac from file
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)
# Deploy options
$deployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployOptions.IncludeCompositeObjects = $true
I know I can input these just fine with SqlPackage.exe, and maybe that’s what I should do. But no where in the documentation or web grok can I find an example of DacServices usage with SQLCMD variables as an option–SQLCMD variables as required parameters for my project’s DACPAC.
You should set options in the $deployOptions.SqlCommandVariableValues property. This is an updateabase Dictionary – you can’t assign a new dictionary but you can update the key/value pairs inside it. For example to set a variable “MyDatabaseRef” to “Database123” use
The API reference is here.