PowerShell Using DacServices With SQLCMD Variables To Deploy A DACPAC

Question

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

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.

Kevin Cunnane · Accepted Answer · 2017-08-16T19:54:06.070

10

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");

The API reference is here.

edited Aug 16 '17 at 19:54

answered Feb 20 '15 at 08:06

Kevin Cunnane

8,020
1
32
35

I saw that reference as well, but SqlCmdVariables is readonly. Unless you know a way around that (reflection?) it won't work. My experience with PowerShell is limited in this regard. – Jon Feb 23 '15 at 15:10
I've updated the answer to clarify that while you can't update the dictionary reference, you can set values within it. This should solve your issue. – Kevin Cunnane Feb 23 '15 at 18:55
1

When I try that, I get 'Cannot index into a null array.' is there anything special that needs to be done to enable / create the SqlCmdVariables object? – dougajmcdonald May 14 '15 at 11:17
2

@KevinCunnane, using the Add method on SqlCommandVariablesValues works for me, so something like this should do it: $deployOptions.SqlCmdVariableValues.Add("MyDatabaseRef", "Database123") – David Peters Jul 10 '15 at 17:56
@DavidPeters thank you - I have updated the answer to reflect this information. Appreciate the help! – Kevin Cunnane Jul 11 '15 at 00:22
How can I use the same variable when I extract dacpac is there any option? – Developer Apr 26 '17 at 07:34
@Dotnet I don't believe that's supported. At the moment you'd need to import into a project and update there. – Kevin Cunnane Apr 27 '17 at 12:25
I didn't get that can you let me know how can I overcome this, question on the same I posed here http://stackoverflow.com/questions/43627068/sqlpackage-exe-passing-sqlcmd-variable-while-extracting-dacpac – Developer May 02 '17 at 05:39

score 0 · Answer 2 · answered Jun 26 '18 at 13:11

I have another code snippet to share in relation to this, a method of processing multiple variables from a Powershell script argument;

param(
[hashtable] $SqlCmdVar
)

$deployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions

# Process the Sql Command Variables
#
if ($SqlCmdVar -ne $null)
{
    foreach($key in $SqlCmdVar.keys)
    {
        Write-Verbose -Message "Adding Sql Command Variable ""$key""..."

        $deployOptions.SqlCommandVariableValues.Add($key,$SqlCmdVar[$key])
    }
}

You would call the script like this;

myscript.ps1 -SqlCmdVar @{ variable1 = "my first value"; variable2 = "my second value"; variableetc = "more values"}

PowerShell Using **DacServices** With SQLCMD Variables To Deploy A DACPAC

Abbreviated Sample

2 Answers2

PowerShell Using DacServices With SQLCMD Variables To Deploy A DACPAC