internal/functions/invoke-sqlpackage.ps1


<#
    .SYNOPSIS
        Invoke the sqlpackage executable
         
    .DESCRIPTION
        Invoke the sqlpackage executable and pass the necessary parameters to it
         
    .PARAMETER Action
        Can either be import or export
         
    .PARAMETER DatabaseServer
        The name of the database server
         
        If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN).
         
        If Azure use the full address to the database server, e.g. server.database.windows.net
         
    .PARAMETER DatabaseName
        The name of the database
         
    .PARAMETER SqlUser
        The login name for the SQL Server instance
         
    .PARAMETER SqlPwd
        The password for the SQL Server user
         
    .PARAMETER TrustedConnection
        Should the sqlpackage work with TrustedConnection or not
         
    .PARAMETER FilePath
        Path to the file, used for either import or export
         
    .PARAMETER Properties
        Array of all the properties that needs to be parsed to the sqlpackage.exe
         
    .PARAMETER DiagnosticFile
        Path to where you want the SqlPackage to output a diagnostics file to assist you in troubleshooting
         
    .PARAMETER ModelFile
        Path to the model file that you want the SqlPackage.exe to use instead the one being part of the bacpac file
         
        This is used to override SQL Server options, like collation and etc
         
    .PARAMETER MaxParallelism
        Sets SqlPackage.exe's degree of parallelism for concurrent operations running against a database
         
        The default value is 8
         
    .PARAMETER PublishFile
        Path to the profile / publish xml file that contains all the advanced configuration instructions for the SqlPackage
         
        Used only in combination with the Publish action
         
    .PARAMETER LogPath
        The path where the log file(s) will be saved
         
    .PARAMETER ShowOriginalProgress
        Instruct the cmdlet to show the standard output in the console
         
        Default is $false which will silence the standard output
         
    .PARAMETER OutputCommandOnly
        Instruct the cmdlet to only output the command that you would have to execute by hand
         
        Will include full path to the executable and the needed parameters based on your selection
         
    .PARAMETER EnableException
        This parameters disables user-friendly warnings and enables the throwing of exceptions
        This is less user friendly, but allows catching exceptions in calling scripts
         
    .EXAMPLE
        PS C:\> $BaseParams = @{
        DatabaseServer = $DatabaseServer
        DatabaseName = $DatabaseName
        SqlUser = $SqlUser
        SqlPwd = $SqlPwd
        }
         
        PS C:\> $ImportParams = @{
        Action = "import"
        FilePath = $BacpacFile
        }
         
        PS C:\> Invoke-SqlPackage @BaseParams @ImportParams
         
        This will start the sqlpackage.exe file and pass all the needed parameters.
         
    .NOTES
        Author: Mötz Jensen (@splaxi)
         
#>

function Invoke-SqlPackage {
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param (
        [ValidateSet("Import", "Export", "Publish")]
        [string] $Action,

        [string] $DatabaseServer,

        [string] $DatabaseName,

        [string] $SqlUser,

        [string] $SqlPwd,

        [string] $TrustedConnection,

        [string] $FilePath,

        [string[]] $Properties,

        [string] $DiagnosticFile,

        [string] $ModelFile,

        [int] $MaxParallelism,

        [Alias("ProfileFile")]
        [string] $PublishFile,

        [string] $LogPath,

        [switch] $ShowOriginalProgress,

        [switch] $OutputCommandOnly,

        [switch] $EnableException
    )

    $executable = $Script:SqlPackagePath

    Invoke-TimeSignal -Start

    if (!(Test-PathExists -Path $executable -Type Leaf)){
        try{
            $envSqlPackage = (Get-Command -Name "sqlpackage.exe").Source
            if (!(Test-PathExists -Path $envSqlPackage -Type Leaf)) { return }
            else{
                $executable = $envSqlPackage
                Set-D365SqlPackagePath -Path $executable
            }
        }
        catch
        {
            # SqlPackage.exe is not in $Script:SqlPackagePath
            # and not in %PATH%, so
            return
        }
    }

    Write-PSFMessage -Level Verbose -Message "Starting to prepare the parameters for sqlpackage.exe"

    [System.Collections.ArrayList]$Params = New-Object -TypeName "System.Collections.ArrayList"

    if ($Action -eq "export") {
        $null = $Params.Add("/Action:export")
        $null = $Params.Add("/SourceServerName:$DatabaseServer")
        $null = $Params.Add("/SourceDatabaseName:$DatabaseName")
        $null = $Params.Add("/SourceTrustServerCertificate:True")
        $null = $Params.Add("/TargetFile:`"$FilePath`"")
        $null = $Params.Add("/Properties:CommandTimeout=0")

        if (!$UseTrustedConnection) {
            $null = $Params.Add("/SourceUser:$SqlUser")
            $null = $Params.Add("/SourcePassword:$SqlPwd")
        }

        Remove-Item -Path $FilePath -ErrorAction SilentlyContinue -Force
    }
    elseif ($Action -eq "import") {
        $null = $Params.Add("/Action:import")
        $null = $Params.Add("/TargetServerName:$DatabaseServer")
        $null = $Params.Add("/TargetDatabaseName:$DatabaseName")
        $null = $Params.Add("/TargetTrustServerCertificate:True")
        $null = $Params.Add("/SourceFile:`"$FilePath`"")
        $null = $Params.Add("/Properties:CommandTimeout=0")

        if (!$UseTrustedConnection) {
            $null = $Params.Add("/TargetUser:$SqlUser")
            $null = $Params.Add("/TargetPassword:$SqlPwd")
        }
    }
    elseif ($Action -eq "publish") {
        $Params.Add("/Action:Publish") > $null
        $Params.Add("/TargetServerName:$DatabaseServer") > $null
        $Params.Add("/TargetDatabaseName:$DatabaseName") > $null
        $Params.Add("/TargetTrustServerCertificate:True") > $null
        $Params.Add("/SourceFile:`"$FilePath`"") > $null
        $Params.Add("/Properties:CommandTimeout=0") > $null

        if (-not $UseTrustedConnection) {
            $Params.Add("/TargetUser:$SqlUser") > $null
            $Params.Add("/TargetPassword:$SqlPwd") > $null
        }

        if ($PublishFile) {
            $Params.Add("/Profile:`"$PublishFile`"") > $null
        }
    }

    foreach ($item in $Properties) {
        $Params.Add("/Properties:$item") > $null
    }

    if ($DiagnosticFile) {
        $Params.Add("/Diagnostics:true") > $null
        $Params.Add("/DiagnosticsFile:`"$DiagnosticFile`"") > $null
    }

    if ($ModelFile) {
        $Params.Add("/ModelFilePath:`"$ModelFile`"") > $null
    }

    if ($MaxParallelism) {
        $Params.Add("/MaxParallelism:$MaxParallelism") > $null
    }

    $result = Invoke-Process -Path $executable -Params "/Version"
    $version = $result.stdout -replace "`r`n", ""
    Write-PSFMessage -Level Verbose -Message "Using SQLPackage version $version"

    Invoke-Process -Executable $executable -Params $params -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly -LogPath $LogPath

    if (Test-PSFFunctionInterrupt) {
        Write-PSFMessage -Level Critical -Message "The SqlPackage.exe exited with an error."
        Stop-PSFFunction -Message "Stopping because of errors." -StepsUpward 1
        return
    }

    Invoke-TimeSignal -End
}