PowerShell Using DacServices With SQLCMD Variables To Deploy A DACPAC

Question 1

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.

Abbreviated Sample

# 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

1

2

3

4

5

6

7

8

9

10

# 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.

Question 2

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

$deployOptions.SqlCommandVariableValues.Add("MyDatabaseRef", "Database123");

1 2	$deployOptions.SqlCommandVariableValues.Add("MyDatabaseRef", "Database123");

The API reference is here.