internal/Get-SqlCmdVars.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
function Get-SqlCmdVars {
    <#
        .SYNOPSIS
            Retrieves the values of PowerShell parameters and updates values of SqlmdVars listed in the publish.xml.
 
        .DESCRIPTION
            Attempt to resolve SQLCmd variables via matching powershell variables explicitly defined in the current context.
            To try and avoid 'bad' default values getting deployed, block a deployment if we have SqlCmd variables that aren't defined in current context.
            Function has one reference and is executed when the "getSqlCmdVars" switch is included.
        .PARAMETER SqlCommandVariableValues
            Mandatory. The SqlCommandVariableValues from the DeployOptions property in the Microsoft.SqlServer.Dac.DacProfile
        .PARAMETER EnableException
            By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
            This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
            Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
        .NOTES
            Author: Richie lee (@bzzzt_io)
 
            Website: https://dbatools.io
            Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
            License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
        .LINK
            https://dbatools.io/Test-Noun
 
        .EXAMPLE
        Imagine content of MyDbProject.publish.xml is as follows -
        <?xml version="1.0" encoding="utf-8"?>
        <Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
        <PropertyGroup>
            <IncludeCompositeObjects>True</IncludeCompositeObjects>
            <TargetDatabaseName>MyDbProject</TargetDatabaseName>
            <DeployScriptFileName>MyDbProject.sql</DeployScriptFileName>
            <TargetConnectionString>Data Source=.;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
            <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
            <CreateNewDatabase>False</CreateNewDatabase>
            <ProfileVersionNumber>1</ProfileVersionNumber>
        </PropertyGroup>
        <ItemGroup>
            <SqlCmdVariable Include="DeployTag">
            <Value>OldValue</Value>
            </SqlCmdVariable>
        </ItemGroup>
        </Project>
        We will need one PowerShell parameter named $DeployTag to update the value
 
        The following scenario will fail as no $deployTag -
        "
            $publishXml = "C:\MyDbProject\bin\Debug\MyDbProject.publish.xml"
            $dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publishXml)
            Get-SqlCmdVars $dacProfile.DeployOptions.SqlCommandVariableValues -EnableException
        "
        This scenario will pass.
        "
            $deployTag = "NewValue"
            $publishXml = "C:\MyDbProject\bin\Debug\MyDbProject.publish.xml"
            $dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publishXml)
            Get-SqlCmdVars $dacProfile.DeployOptions.SqlCommandVariableValues -EnableException
        "
    #>

    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        $SqlCommandVariableValues,
        [switch]$EnableException
    )
    $missingVariables = @()
    $keys = $($SqlCommandVariableValues.Keys)
    foreach ($var in $keys) {
        if (Test-Path variable:$var) {
            $value = Get-Variable $var -ValueOnly
            $SqlCommandVariableValues[$var] = $value
        }
        else {
            $missingVariables += $var
        }
    }
    if ($missingVariables.Count -gt 0) {
        $errorMsg = 'The following SqlCmd variables are not defined in the session (but are defined in the publish profile): {0}' -f ($missingVariables -join " `n")
        Stop-Function -Message $errorMsg -EnableException $EnableException
    }
}