Public/Invoke-SqlPackage.ps1
|
function Invoke-SqlPackage { <# .SYNOPSIS Executes SQLCMD .DESCRIPTION Wrapper tp the commandline tool SQLPACKAGE. It provides parameter validation, output and error handling. .NOTES Check https://github.com/abbgrade/PsDac and https://github.com/abbgrade/PsSmo if one of them already supports your use case. They provide better PowerShell integration. .LINK https://docs.microsoft.com/de-de/sql/tools/sqlpackage?view=sql-server-ver15 #> [CmdletBinding()] param( # Flag if a install script should be created. [Parameter( Mandatory, ParameterSetName = 'Script' )] [string] $Script, # Flag if a database should be published. [Parameter( Mandatory, ParameterSetName = 'Publish' )] [switch] $Publish, [Parameter( Mandatory, ParameterSetName = 'Extract' )] [switch] $Extract, # Path to the dacpac file. [Parameter( Mandatory, ParameterSetName = 'Publish' )] [Parameter( Mandatory = $false, ParameterSetName = 'Extract' )] # [ValidateScript({ $_.Exists })] [System.IO.FileInfo] $DacPac, # Name of the SQL Server Instance to publish the dacpac to. [Parameter( Mandatory, ParameterSetName = 'Publish', ValueFromPipelineByPropertyName )] [Alias('ServerInstance', 'DataSource')] [ValidateNotNullOrEmpty()] [string] $TargetServerName, # Username for the login. [Parameter( Mandatory = $false, ValueFromPipelineByPropertyName )] [Alias('Username')] [string] $TargetUser, # Password for the login. [Parameter( Mandatory = $false, ValueFromPipelineByPropertyName )] [Alias('Password')] [string] $TargetPassword, # Name of the SQL database to publish the dacpac to. [Parameter( Mandatory, ParameterSetName = 'Publish', ValueFromPipelineByPropertyName )] [Alias('DatabaseName', 'Database')] [ValidateNotNullOrEmpty()] [string] $TargetDatabaseName, # Name of the SQL Server Instance to publish the dacpac to. [Parameter( Mandatory, ParameterSetName = 'Extract', ValueFromPipelineByPropertyName )] [ValidateNotNullOrEmpty()] [string] $SourceServerName, # Username for the login. [Parameter( Mandatory = $false, ValueFromPipelineByPropertyName )] [string] $SourceUser, # Password for the login. [Parameter( Mandatory = $false, ValueFromPipelineByPropertyName )] [string] $SourcePassword, # AccessToken for the login [Parameter( ValueFromPipelineByPropertyName )] [ValidateNotNullOrEmpty()] [string] $AccessToken, # Name of the SQL database to publish the dacpac to. [Parameter( Mandatory, ParameterSetName = 'Extract', ValueFromPipelineByPropertyName )] [ValidateNotNullOrEmpty()] [string] $SourceDatabaseName, # Flag if the SQL Server is a Azure SQL Server. [Parameter( ValueFromPipelineByPropertyName )] [ValidateNotNullOrEmpty()] [switch] $AzureSql, # Flag if interactive authentication is used. [Parameter( ValueFromPipelineByPropertyName )] [ValidateNotNullOrEmpty()] [switch] $InteractiveAuthentication, # Flag if surplus contraints should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropConstraintsNotInSource = $false, # Flag if surplus triggers should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropDmlTriggersNotInSource = $false, # Flag if surplus properties should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropExtendedPropertiesNotInSource = $false, # Flag if surplus indices should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropIndexesNotInSource = $false, # Flag if surplus objects should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropObjectsNotInSource = $false, # Flag if surplus permissions should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropPermissionsNotInSource = $false, # Flag if surplus role members should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropRoleMembersNotInSource = $false, # Flag if surplus statistics should be dropped. [ValidateNotNullOrEmpty()] [bool] $DropStatisticsNotInSource = $false, # A semicolon-delimited list of object types that should be ignored during deployment [Parameter( Mandatory = $false, ParameterSetName = 'Publish', ValueFromPipelineByPropertyName )] [Parameter( Mandatory = $false, ParameterSetName = 'Script', ValueFromPipelineByPropertyName )] [string[]] $ExcludeObjectTypes, # Timeout is seconds for the execution. [ValidateNotNullOrEmpty()] [int] $Timeout, # Values for the variables used in the dacpac. [ValidateNotNull()] [hashtable] $Variables = @{}, # Force the action and accept the risk of data loss. [Parameter( ParameterSetName = 'Publish' )] [switch] $Force ) # soon... # Write-Warning 'This cmdlet is deprecated use PsDac instead' if ( -not $Global:SqlPackage ) { throw "SqlPackage is not inizialized. Please run Initialize-SqlPackage." } if ( $TargetServerName.Contains('.database.windows.net') -and -not $AzureSql ) { Write-Verbose 'Overwrite AzureSql flag to enabled.' $AzureSql = $true } $arguments = @() switch ( $PSCmdlet.ParameterSetName ) { Publish { $arguments += '/Action:Publish' $arguments += "/SourceFile:""$DacPac""" if ( $AzureSql ) { if ( $AccessToken ) { $arguments += "/AccessToken:$AccessToken" $arguments += "/TargetDatabaseName:""$TargetDatabaseName""" $arguments += "/TargetServerName:""$TargetServerName""" } else { $Authentication = 'Active Directory Integrated' if ( $InteractiveAuthentication ) { $Authentication = 'Active Directory Interactive' } $arguments += "/TargetConnectionString:""Server='$TargetServerName';Authentication=$Authentication;Database='$TargetDatabaseName';""" } } else { $arguments += "/TargetDatabaseName:""$TargetDatabaseName""" $arguments += "/TargetServerName:""$TargetServerName""" if ( $TargetUser ) { $arguments += "/TargetUser:""$TargetUser""" $arguments += "/TargetPassword:""$TargetPassword""" } if ( $Timeout -ne $null ) { $arguments += "/TargetTimeout:$Timeout" } } $arguments += "/p:DropConstraintsNotInSource=""$DropConstraintsNotInSource""" $arguments += "/p:DropDmlTriggersNotInSource=""$DropDmlTriggersNotInSource""" $arguments += "/p:DropExtendedPropertiesNotInSource=""$DropExtendedPropertiesNotInSource""" $arguments += "/p:DropIndexesNotInSource=""$DropIndexesNotInSource""" $arguments += "/p:DropObjectsNotInSource=""$DropObjectsNotInSource""" $arguments += "/p:DropPermissionsNotInSource=""$DropPermissionsNotInSource""" $arguments += "/p:DropRoleMembersNotInSource=""$DropRoleMembersNotInSource""" $arguments += "/p:DropStatisticsNotInSource=""$DropStatisticsNotInSource""" if ($ExcludeObjectTypes ) { $arguments += "/p:ExcludeObjectTypes=""$( $ExcludeObjectTypes -join ';' )""" } if ( $Force.IsPresent ) { $arguments += '/p:BlockOnPossibleDataLoss=False' } } Extract { $arguments += '/Action:Extract' $arguments += "/TargetFile:""$DacPac""" if ( $AzureSql ) { if ( $AccessToken ) { $arguments += "/AccessToken:$AccessToken" $arguments += "/SourceDatabaseName:""$SourceDatabaseName""" $arguments += "/SourceServerName:""$SourceServerName""" } else { $Authentication = 'Active Directory Integrated' if ( $InteractiveAuthentication ) { $Authentication = 'Active Directory Interactive' } $arguments += "/SourceConnectionString:""Server='$SourceServerName';Authentication=$Authentication;Database='$SourceDatabaseName';""" } } else { $arguments += "/SourceDatabaseName:""$SourceDatabaseName""" $arguments += "/SourceServerName:""$SourceServerName""" if ( $SourceUser ) { $arguments += "/SourceUser:""$SourceUser""" $arguments += "/SourcePassword:""$SourcePassword""" } if ( $Timeout -ne $null ) { $arguments += "/SourceTimeout:$Timeout" } } } default { throw "ParameterSet $( $PSCmdlet.ParameterSetName ) not implemented." } } foreach ( $variable in $Variables.GetEnumerator() ) { $arguments += "/Variables:$( $variable.Key )=""$( $variable.Value )""" } #region Prepare process $process = New-Object System.Diagnostics.Process $process.StartInfo.FileName = $Global:SqlPackage $process.StartInfo.RedirectStandardError = $true $process.StartInfo.RedirectStandardOutput = $true $process.StartInfo.UseShellExecute = $false $process.StartInfo.CreateNoWindow = $true $process.StartInfo.Arguments = $arguments #endregion Write-Verbose "$( $process.StartInfo.FileName ) $( $process.StartInfo.Arguments )" #region Start process $outputBuffer = New-Object System.Text.StringBuilder $errorBuffer = New-Object System.Text.StringBuilder $sScripBlock = { if (! [String]::IsNullOrEmpty($EventArgs.Data)) { $Event.MessageData.AppendLine($EventArgs.Data) } } try { $outputEvent = Register-ObjectEvent -InputObject $process ` -Action $sScripBlock -EventName 'OutputDataReceived' ` -MessageData $outputBuffer $errorEvent = Register-ObjectEvent -InputObject $process ` -Action $sScripBlock -EventName 'ErrorDataReceived' ` -MessageData $errorBuffer $process.Start() | Out-Null $process.BeginOutputReadLine(); $process.BeginErrorReadLine(); $timeoutMS = ($Timeout + 5 ) * 1000 if ( -not $Timeout ) { $process.WaitForExit() $returnCode = $process.ExitCode } elseif ( $process.WaitForExit( $timeoutMS ) ) { $returnCode = $process.ExitCode } elseif ( $process.HasExited ) { $returnCode = $process.ExitCode } else { Write-Verbose "Invoke-SqlPackage: Timeout $Timeout reached." $process.Kill() $returnCode = -1 } } finally { Unregister-Event -SourceIdentifier $outputEvent.Name Unregister-Event -SourceIdentifier $errorEvent.Name } #endregion #region handle result $standardOutput = $outputBuffer.ToString().Trim() $standardError = $errorBuffer.ToString().Trim() $process.Close() | Out-Null Write-Verbose "Invoke-SqlPackage: '$scriptName' Output $( $standardOutput.Length ) chars: $standardOutput" Write-Verbose "Invoke-SqlPackage: '$scriptName' Error $( $standardError.Length ) chars: $standardError" if ( $standardError.EndsWith('Timeout expired') ) { Write-Verbose "Invoke-SqlPackage: Timeout $Timeout reached." $returnCode = -1 } if ( $returnCode -ne 0 ) { throw "SqlPackage exit code $returnCode; err: '$standardError'." } #endregion } |