SqlDataTools

2.2.0.0

SqlDataTools.psm1

Set-StrictMode -Version Latest

#Region Global Configuration
try {
    $Script:PSData = $null
}
catch {
    throw $_
}
#EndRegion

#Region Enumerations
enum Mode {
    Export
    Import
    Merge
}

enum Encoding {
    ASCII
    BigEndianUnicode
    BigEndianUTF32
    OEM
    Unicode
    UTF7
    UTF8
    UTF8BOM
    UTF8NoBOM
    UTF32
}
#EndRegion

#Region Classes
Class ArgumentCompleterResult {
    #properties

    #Method
    static [System.Management.Automation.CompletionResult[]] GetArgumentCompleterResult([System.Array]$Arguments) {
        #[string]$CommandName = $Arguments[0]
        [string]$ParameterName = $Arguments[1]
        [string]$WordToComplete = $Arguments[2]
        #[System.Management.Automation.Language.CommandAst]$CommandAst = $Arguments[3]
        [Hashtable]$FakeBoundParameters = $Arguments[4]

        $ParameterValueList = $null

        switch ($ParameterName) {
            'DataTemplateName' {
                $TemplateList = [System.Collections.Generic.List[string]]::New()

                if ($FakeBoundParameters.ContainsKey('Mode')) {
                    switch ($FakeBoundParameters['Mode']) {
                        'Export' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Export.Keys)
                        }
                        'Import' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Import.Keys)

                        }
                        'Merge' {
                            $TemplateList.AddRange([string[]]$Script:PSData.Merge.Keys)
                        }
                    }
                } else {
                    $TemplateList.AddRange([string[]]$Script:PSData.Export.Keys)
                    $TemplateList.AddRange([string[]]$Script:PSData.Merge.Keys)
                }

                $ParameterValueList = $TemplateList
            }
            Default {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Unknown argument completer.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidType,
                    $ParameterName
                )
            }
        }

        $SelectedParameters = $ParameterValueList.where({ $_ -like "$WordToComplete*" }) | Sort-Object

        $CompletionResultList = [System.Collections.Generic.List[System.Management.Automation.CompletionResult]]::New()

        foreach ($Parameter in $SelectedParameters) {
            if ($Parameter -match '\s|''') {
                $CompletionText = [string]::Format("'{0}'", $($Parameter.Replace("'", "''")))
            } else {
                $CompletionText = $Parameter
            }

            $ListItemText = $Parameter
            $ResultType = [System.Management.Automation.CompletionResultType]::ParameterValue
            $ToolTip = $Parameter

            $CompletionResultList.Add([System.Management.Automation.CompletionResult]::New($CompletionText, $ListItemText, $ResultType, $ToolTip))
        }

        return $CompletionResultList
    }
}

Class ExportConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Export) {
            return @()
        } else {
            return [string[]]$Script:PSData.Export.Keys
        }
    }
}

Class ImportConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Import) {
            return @()
        } else {
            return [string[]]$Script:PSData.Import.Keys
        }
    }
}

Class MergeConfigurationTemplates : System.Management.Automation.IValidateSetValuesGenerator {
    [string[]] GetValidValues() {
        if ($null -eq $Script:PSData) {
            throw [System.Management.Automation.ErrorRecord]::New(
                [Exception]::New('Template repository not loaded. Use the Use-TemplateRepository function to load template repository.'),
                '1',
                [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                $null
            )
        } elseif ($null -eq $Script:PSData.Merge) {
            return @()
        } else {
            return [string[]]$Script:PSData.Merge.Keys
        }
    }
}

class ValidatePathExists : System.Management.Automation.ValidateArgumentsAttribute {
    [string]$PathType = 'Any'

    ValidatePathExists([string]$PathType) {
        $this.PathType = $PathType
    }

    [void]Validate([object]$Path, [System.Management.Automation.EngineIntrinsics]$EngineIntrinsics) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if(-not (Test-Path -Path $Path -PathType $this.PathType)) {
            switch ($this.PathType) {
                'Container' {
                    throw [System.IO.DirectoryNotFoundException]::New()
                }
                'Leaf' {
                    throw [System.IO.FileNotFoundException]::New()
                }
                Default {
                    throw [System.InvalidOperationException]::New('An unexpected error has occurred.')
                }
            }
        }
    }
}

class ValidatePathIsValid : System.Management.Automation.ValidateArgumentsAttribute {
    [void]Validate([object]$Path, [System.Management.Automation.EngineIntrinsics]$EngineIntrinsics) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if ($Path -is [System.IO.DirectoryInfo]) {
            if (-not $(Test-Path -LiteralPath $Path.Parent -PathType Container)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("Cannot find path '$Path.Directory' because it does not exist."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            } elseif (-not $(Test-Path -LiteralPath $Path -PathType Container -IsValid)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("The path '$Path.Name' is not valid."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            }
        }

        if ($Path -is [System.IO.FileInfo]) {
            if (-not $(Test-Path -LiteralPath $Path.Directory -PathType Container)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("Cannot find path '$Path.Directory' because it does not exist."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            } elseif (-not $(Test-Path -LiteralPath $Path -PathType Leaf -IsValid)) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [System.ArgumentException]::New("The path '$Path.Name' is not valid."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Path
                )
            }
        }
    }
}

class TextFieldParser {
    [System.IO.StreamReader]$StreamReader
    [Microsoft.VisualBasic.FileIO.TextFieldParser]$TextFieldParser
    [char]$Delimiter
    [switch]$IgnoreQuotes
    [string[]]$Headers
    [int]$CurrentLine = 0
    [int]$TotalLines = 0

    TextFieldParser([string]$Path, [Encoding]$Encoding, [char]$Delimiter, [switch]$IgnoreQuotes) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if (-not (Test-Path -Path $Path -PathType Leaf)) {
            throw [System.IO.FileNotFoundException]::New("Cannot find path '$Path' because it does not exist.")
        }

        try {
            $FileStream = [System.IO.FileStream]::New($Path, [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read, [System.IO.FileShare]::Read)

            $this.StreamReader = [System.IO.StreamReader]::New($FileStream, $Encoding)
            $this.Delimiter = $Delimiter
            $this.IgnoreQuotes = $IgnoreQuotes

            $this.TotalLines = 0

            while ($null -ne $this.StreamReader.ReadLine()) {
                $this.TotalLines++
            }

            [void]$this.StreamReader.BaseStream.Seek(0, [System.IO.SeekOrigin]::Begin)
            $this.StreamReader.DiscardBufferedData()

            $TextEncoding = [System.Text.Encoding]::$Encoding

            switch ($Encoding) {
                'BigEndianUTF32' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding('utf-32BE')
                }
                'OEM' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding([System.Globalization.CultureInfo]::CurrentCulture.TextInfo.OEMCodePage)
                }
                'UTF8BOM' {
                    $TextEncoding = [System.Text.Encoding]::UTF8
                }
                'UTF8NoBOM' {
                    $TextEncoding = [System.Text.Encoding]::New([System.Text.UTF8Encoding]::New($false))
                }
                Default {
                    if ([System.Text.Encoding]::$Encoding -is [System.Text.Encoding]) {
                        $TextEncoding = [System.Text.Encoding]::$Encoding
                    } else {
                        throw [System.Management.Automation.ErrorRecord]::New(
                            [Exception]::New("Invalid encoding specified: $Encoding"),
                            '1',
                            [System.Management.Automation.ErrorCategory]::NotImplemented,
                            $Encoding
                        )
                    }
                }
            }

            $this.TextFieldParser = [Microsoft.VisualBasic.FileIO.TextFieldParser]::New($FileStream, $TextEncoding)

            $this.TextFieldParser.SetDelimiters($Delimiter)

            if ($IgnoreQuotes) {
                $this.TextFieldParser.HasFieldsEnclosedInQuotes = $false
            }

            $this.Headers = $this.TextFieldParser.ReadFields()
        }
        catch {
            throw $_
        }
    }

    TextFieldParser([string]$Path, [Encoding]$Encoding, [char]$Delimiter, [switch]$IgnoreQuotes, [string[]]$RequiredColumnNames) {
        if([string]::IsNullOrWhiteSpace($Path)) {
            throw [System.ArgumentNullException]::New()
        }

        if (-not (Test-Path -Path $Path -PathType Leaf)) {
            throw [System.IO.FileNotFoundException]::New("Cannot find path '$Path' because it does not exist.")
        }

        try {
            $FileStream = [System.IO.FileStream]::New($Path, [System.IO.FileMode]::Open, [System.IO.FileAccess]::Read, [System.IO.FileShare]::Read)

            $this.StreamReader = [System.IO.StreamReader]::New($FileStream, $Encoding)
            $this.Delimiter = $Delimiter
            $this.IgnoreQuotes = $IgnoreQuotes

            $this.TotalLines = 0

            while ($null -ne $this.StreamReader.ReadLine()) {
                $this.TotalLines++
            }

            [void]$this.StreamReader.BaseStream.Seek(0, [System.IO.SeekOrigin]::Begin)
            $this.StreamReader.DiscardBufferedData()

            $TextEncoding = [System.Text.Encoding]::$Encoding

            switch ($Encoding) {
                'BigEndianUTF32' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding('utf-32BE')
                }
                'OEM' {
                    $TextEncoding = [System.Text.Encoding]::GetEncoding([System.Globalization.CultureInfo]::CurrentCulture.TextInfo.OEMCodePage)
                }
                'UTF8BOM' {
                    $TextEncoding = [System.Text.Encoding]::UTF8
                }
                'UTF8NoBOM' {
                    $TextEncoding = [System.Text.Encoding]::New([System.Text.UTF8Encoding]::New($false))
                }
                Default {
                    if ([System.Text.Encoding]::$Encoding -is [System.Text.Encoding]) {
                        $TextEncoding = [System.Text.Encoding]::$Encoding
                    } else {
                        throw [System.Management.Automation.ErrorRecord]::New(
                            [Exception]::New("Invalid encoding specified: $Encoding"),
                            '1',
                            [System.Management.Automation.ErrorCategory]::NotImplemented,
                            $Encoding
                        )
                    }
                }
            }

            $this.TextFieldParser = [Microsoft.VisualBasic.FileIO.TextFieldParser]::New($FileStream, $TextEncoding)

            $this.TextFieldParser.SetDelimiters($Delimiter)

            if ($IgnoreQuotes) {
                $this.TextFieldParser.HasFieldsEnclosedInQuotes = $false
            }

            $this.Headers = $this.TextFieldParser.ReadFields()

            $ComparObjectParameters = @{
                'ReferenceObject' = $this.Headers
                'DifferenceObject' = $RequiredColumnNames
            }

            $MissingProperties = (Compare-Object @ComparObjectParameters).where({$_.SideIndicator -eq "=>"})

            if ($MissingProperties.Count -gt 0) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New("CSV missing required properties: $([string]::Join(', ', $MissingProperties.InputObject))"),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidData,
                    $MissingProperties
                )
            }
        }
        catch {
            throw $_
        }
    }

    [void] Close() {
        $this.TextFieldParser.Close()
        $this.StreamReader.Close()
        $this.StreamReader.BaseStream.Close()
    }

    [PSCustomObject] ReadLine() {
        $this.CurrentLine = $this.TextFieldParser.LineNumber

        $Fields = $this.TextFieldParser.ReadFields();

        if ($null -eq $Fields) {
            return $null
        }

        $Row = [PSCustomObject]::New()

        for ($i = 0; $i -lt $Fields.Count; $i++) {
            $Row.PSObject.Properties.Add(
                [PSNoteProperty]::new($this.Headers[$i], $Fields[$i])
            )
        }

        return $Row
    }

    [bool] IsValidRow([PSCustomObject]$Row, [psobject[]]$InputColumns) {
        $WarningFormatString = 'In row {0}, column {1} with the value "{2}" is not a valid value.'
        $IsValidRow = $true
        $Match = $false

        foreach ($InputColumn in $InputColumns) {
            switch ($InputColumn.Keys.where({$_ -NotIn @('DataType', 'Name')})) {
                'ValidatePattern' {
                    $Regex = [System.Text.RegularExpressions.Regex]::New($InputColumn.ValidatePattern)
                    $Match = $Regex.IsMatch($Row.$($InputColumn.Name))

                    Continue
                }
                'ValidateRange' {
                    $TypeObject = [System.Type]::GetType($InputColumn.DataType)

                    $ColumnValue = [System.Management.Automation.LanguagePrimitives]::ConvertTo($Row.$($InputColumn.Name), $TypeObject)

                    $Match = $ColumnValue -ge $InputColumn.ValidateRange[0] -and $ColumnValue -le $InputColumn.ValidateRange[1]

                    Continue
                }
                'ValidateSet' {
                    $Match = $Row.$($InputColumn.Name) -In $InputColumn.ValidateSet

                    Continue
                }
                Default {
                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New('Unknown validate method.'),
                        '1',
                        [System.Management.Automation.ErrorCategory]::InvalidOperation,
                        $InputColumn
                    )
                }
            }

            if (-not $Match) {
                Write-Warning $([string]::Format($WarningFormatString, $this.CurrentLine, $InputColumn.Name, $Row.$($InputColumn.Name)))

                $IsValidRow = $false
            }
        }

        return $IsValidRow
    }
}

class TransformPath : System.Management.Automation.ArgumentTransformationAttribute {
    [object]Transform([System.Management.Automation.EngineIntrinsics]$EngineIntrinsics, [object]$InputData) {
        if ([System.IO.Path]::IsPathRooted($InputData)) {
            return $InputData
        } else {
            return [System.IO.Path]::GetFullPath([System.IO.Path]::Combine($PWD.Path, $InputData))
        }

        throw [System.InvalidOperationException]::New('Unexpected error.')
    }
}
#EndRegion

#Region Type Definitions
$TypeDefinition = @'
using System;
namespace SqlDataTools
{
    public class SqlDataTemplate
    {
        public string Mode;
        public string Name;
        public string Description;
    }
}
'@

Add-Type -TypeDefinition $TypeDefinition

Remove-Variable -Name TypeDefinition
#EndRegion


function Export-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'Low',
        DefaultParameterSetName = 'ServerInstance_Template'
    )]

    [OutputType([System.Void])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Template'
        )]
        [ValidateSet([ExportConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [ValidateLength(1, 128)]
        [string]$TableName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathIsValid()]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Template'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Template'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Switch]$Force
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance_Table', 'ServerInstance_Template')
        $TemplateParameterSets = @('ServerInstance_Template', 'SqlConnection_Template')

        try {
            if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                $Configuration = $Script:PSData.Export."$DataTemplateName"

                $CsvObjectParameters = [System.Collections.ArrayList]::New()

                foreach ($Enumerator in $Configuration.OutputObject.Property.GetEnumerator()) {
                    $Hashtable = @{
                        N = $Enumerator.Name
                        E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                    }

                    [void]$CsvObjectParameters.Add($Hashtable)
                }

                $SqlDataAdapterCommandSet = $Configuration.SqlDataAdapterCommandSet
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            $SqlClientDataSetParameters = @{
                'SqlConnection' = $SqlConnection
                'DataSetName' = 'SqlDataTools'
                'OutputAs' = 'DataTable'
            }

            if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                $SqlClientDataSetParameters.Add('SqlCommand', $SqlDataAdapterCommandSet.Select.CommandText)
            } else {
                $SqlClientDataSetParameters.Add('SqlCommand', $TableName)
                $SqlClientDataSetParameters.Add('CommandType', [System.Data.CommandType]::TableDirect)
            }

            $DataTable = Get-SqlClientDataSet @SqlClientDataSetParameters

            $ExportCsvParameters = @{
                'Path' = $Path
                'NoTypeInformation' = $true
            }

            if ($PSBoundParameters.ContainsKey('Encoding')) {
                $ExportCsvParameters.Add('Encoding', [string]$Encoding)
            }

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $ExportCsvParameters.Add('Delimiter', $Delimiter)
            }

            if ($Force) {
                $ExportCsvParameters.Add('Force', $true)
            } else {
                $ExportCsvParameters.Add('NoClobber', $true)
            }

            if ($PSCmdlet.ShouldProcess($DatabaseName, "Export $DataTemplateName")) {
                if ($PSCmdlet.ParameterSetName -in $TemplateParameterSets) {
                    $DataTable | Select-Object $CsvObjectParameters | Export-Csv @ExportCsvParameters
                } else {
                    $DataTable | Export-Csv @ExportCsvParameters
                }
            }
        }
        catch {
            throw $_
        }
        finally {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Get-SqlDataTemplate {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $true,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([SqlDataTools.SqlDataTemplate])]

    param (
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 0
        )]
        [Mode]$Mode,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 1
        )]
        [ArgumentCompleter({ [ArgumentCompleterResult]::GetArgumentCompleterResult($Args) })]
        [string]$DataTemplateName

    )

    begin {
        try {
            if ($null -eq $Script:PSData) {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Module not initialized.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::ResourceUnavailable,
                    $null
                )
            }

            $Configuration = $Script:PSData
        }
        catch {
            throw
        }
    }

    process {
        $ModeNames = [System.Enum]::GetNames([Mode]) | Sort-Object

        foreach ($ModeName in $ModeNames) {
            if ($PSBoundParameters.ContainsKey('Mode')) {
                if ($ModeName -ne $Mode) {
                    Continue
                }
            }

            $TemplateKeys = $Configuration[$ModeName].Keys | Sort-Object

            foreach ($TemplateKey in $TemplateKeys) {
                if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                    if ($DataTemplateName -ne $TemplateKey) {
                        Continue
                    }
                }

                $SqlDataTemplate = [SqlDataTools.SqlDataTemplate]::New()

                $SqlDataTemplate.Mode = $ModeName
                $SqlDataTemplate.Name = $TemplateKey
                $SqlDataTemplate.Description = $Configuration[$ModeName][$TemplateKey].Description

                $SqlDataTemplate
            }
        }
    }

    end {
    }
}

function Import-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'Medium',
        DefaultParameterSetName = 'ServerInstance_DataTemplate'
    )]

    [OutputType([SqlClient.DataSetResult])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [ValidateSet([ImportConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [ValidateLength(1, 128)]
        [string]$TableName,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_DataTemplate'
        )]
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_DataTemplate'
        )]
        [switch]$SkipValidation,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance_Table'
        )]
        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection_Table'
        )]
        [int]$BatchSize = 1000
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance_Table', 'ServerInstance_DataTemplate')

        try {
            if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                $Configuration = $Script:PSData.Import."$DataTemplateName"

                $DataSetTableName = $Configuration.DataTable.TableName

                $PropertyArray = [System.Collections.ArrayList]::New()

                foreach ($Enumerator in $Configuration.InputObject.Property.GetEnumerator()) {
                    $Hashtable = @{
                        N = $Enumerator.Name
                        E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                    }

                    [void]$PropertyArray.Add($Hashtable)
                }

                $CsvObjectParameters = @{
                    'Property' = $PropertyArray
                    'Unique' = $Configuration.InputObject.Unique
                }
            } else {
                $DataSetTableName = [System.IO.Path]::GetFileNameWithoutExtension($Path.Name)

                $CsvObjectParameters = @{
                    'Unique' = $false
                }
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Import SQL Data.'
                'Status' = [string]::Format('Step {0} of {1}', 0, 3)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }

            $ChildProgressParameters = @{
                'Id' = 1
                'ParentId' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            #Region Create Dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 1, 2)
            $ProgressParameters.CurrentOperation = 'Create dataset.'
            $ProgressParameters.PercentComplete = 0 / 2 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $DataSet = [System.Data.DataSet]::New('SqlDataTools')

            $DataTable = [System.Data.DataTable]::New($DataSetTableName)

            if ($PSBoundParameters.ContainsKey('DataTemplateName')) {
                $TableName = $Configuration.DataTable.TableName

                foreach ($Column in $Configuration.DataTable.Columns) {
                    $DataColumn = [System.Data.DataColumn]::New($Column.Name, [System.Type]::GetType($Column.DataType))

                    $DataColumn.AllowDBNull = $Column.AllowDBNull

                    if ($Column.Unique -eq 'True') {
                        $DataColumn.Unique = $true
                    }

                    if ([System.Type]::GetType($Column.DataType) -is [System.String]) {
                        $DataColumn.MaxLength = $Column.MaxLength
                    }

                    [void]$DataTable.Columns.Add($DataColumn)
                }

                $KeyColumns = [System.Collections.Generic.List[System.Data.DataColumn]]::New()

                foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                    $KeyColumns.Add($DataTable.Columns[$PrimaryKey])
                }

                try {
                    $DataTable.PrimaryKey = $KeyColumns
                }
                catch {
                    $ErrorMessage = [string]::Format("These columns do not have unique values in table {0}: {1}", $DataSetTableName, [string]::Join(', ', $KeyColumns.ColumnName))

                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New($ErrorMessage),
                        '1',
                        [System.Management.Automation.ErrorCategory]::InvalidData,
                        $DataSetTableName
                    )
                }
            }

            $DataSet.Tables.Add($DataTable)
            #Endregion

            #Region Setup Bulk Copy Base Parameters
            $SqlClientBulkCopyBaseParameters = @{
                'SqlConnection' = $SqlConnection
                'TableName' = $TableName
                'QueryTimeout' = 0
                'Verbose' = [bool]($PSBoundParameters['Verbose'])
            }

            if ($PSBoundParameters.ContainsKey('BatchSize')) {
                $SqlClientBulkCopyBaseParameters.Add('BatchSize', $BatchSize)
            } elseif ($Configuration.keys -contains 'BatchSize') {
                $SqlClientBulkCopyBaseParameters.Add('BatchSize', $Configuration.BatchSize)
            }

            if ($Configuration.keys -contains 'EnableStreaming') {
                $SqlClientBulkCopyBaseParameters.Add('EnableStreaming', $Configuration.EnableStreaming)
            }

            if ($Configuration.keys -contains 'NotifyAfter') {
                $SqlClientBulkCopyBaseParameters.Add('NotifyAfter', $Configuration.NotifyAfter)
            }

            if ($Configuration.keys -contains 'SqlBulkCopyOptions') {
                $SqlClientBulkCopyBaseParameters.Add('SqlBulkCopyOptions', $Configuration.SqlBulkCopyOptions)
            }

            $ColumnMappingCollection = [System.Collections.Generic.List[Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping]]::New()

            if ($Configuration.keys -contains 'ColumnMappings') {
                foreach ($ColumnMapping in $Configuration.ColumnMappings) {
                    $SqlClientBulkCopyColumnMapping = [Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping]::New($ColumnMapping.SourceColumn, $ColumnMapping.DestinationColumn)

                    $ColumnMappingCollection.Add($SqlClientBulkCopyColumnMapping)
                }

                if ($ColumnMappingCollection.Count -gt 0) {
                    $SqlClientBulkCopyBaseParameters.Add('ColumnMappingCollection', $ColumnMappingCollection)
                }
            }
            #Endregion

            #Region Add CSV data to dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 2, 2)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 1 / 2 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes)
            }

            if (-not $PSBoundParameters.ContainsKey('DataTemplateName')) {
                foreach ($Column in $TextFieldParser.Headers) {
                    [void]$DataTable.Columns.Add($Column.Trim())
                }
            }

            try {
                $LinesPerGB = [Math]::Ceiling(1gb / ($Path.length / $TextFieldParser.TotalLines))

                $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                Write-Verbose $ChildProgressParameters.CurrentOperation
                Write-Progress @ChildProgressParameters

                while (-not $TextFieldParser.TextFieldParser.EndOfData) {
                    $Row = $TextFieldParser.ReadLine()

                    if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                        $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                        $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                        Write-Verbose $ChildProgressParameters.CurrentOperation
                        Write-Progress @ChildProgressParameters
                    }

                    if ($SkipValidation -or $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                        $TransformedRow = $Row | Select-Object @CsvObjectParameters

                        $DataRow = $DataSet.Tables[$DataSetTableName].NewRow()

                        foreach ($Property in $TransformedRow.PsObject.Properties) {
                            if ($null -eq $Property.Value) {
                                $DataRow.Item($Property.Name) = [DBNull]::Value
                            } else {
                                $DataRow.Item($Property.Name) = $Property.Value
                            }
                        }

                        [void]$DataSet.Tables[$DataSetTableName].Rows.Add($DataRow)

                        if ($TextFieldParser.CurrentLine % $LinesPerGB -eq 0 -or $TextFieldParser.TextFieldParser.EndOfData) {
                            $SqlClientBulkCopyParameters = $SqlClientBulkCopyBaseParameters.PSObject.Copy()

                            $SqlClientBulkCopyParameters.Add('DataTable', $DataSet.Tables[$DataSetTableName])

                            if ($PSCmdlet.ShouldProcess($DatabaseName, "Bulk copy")) {
                                Invoke-SqlClientBulkCopy @SqlClientBulkCopyParameters
                            }

                            $DataSet.Tables[$DataSetTableName].Clear()
                        }
                    }
                }
            }
            catch {
                throw $_
            }
            finally {
                Write-Progress -Id 1 -Activity 'Reading delimited file.' -Completed
            }
            #Endregion
        }
        catch {
            throw $_
        }
        finally{
            Write-Progress -Id 1 -Activity 'Copy SQL Data.' -Completed
            Write-Progress -Id 0 -Activity 'Copy SQL Data.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            if (Test-Path -Path Variable:SqlDataAdapter) {
                $SqlDataAdapter.Dispose()
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Merge-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $true,
        ConfirmImpact = 'High',
        DefaultParameterSetName = 'ServerInstance'
    )]

    [OutputType([SqlClient.DataSetResult])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidateSet([MergeConfigurationTemplates])]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance'
        )]
        [ValidateLength(1, 128)]
        [Alias('SqlServer')]
        [string]$ServerInstance,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'ServerInstance'
        )]
        [ValidateLength(1, 128)]
        [Alias('Database')]
        [string]$DatabaseName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ParameterSetName = 'SqlConnection'
        )]
        [Microsoft.Data.SqlClient.SqlConnection]$SqlConnection,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$SkipValidation,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [int]$BatchSize = 1000
    )

    begin {
        $ServerInstanceParameterSets = @('ServerInstance')

        try {
            $Configuration = $Script:PSData.Merge."$DataTemplateName"

            $DataSetTableName = $Configuration.DataTable.TableName

            $PropertyArray = [System.Collections.ArrayList]::New()

            foreach ($Enumerator in $Configuration.InputObject.Property.GetEnumerator()) {
                $Hashtable = @{
                    N = $Enumerator.Name
                    E = [scriptblock]::Create($Enumerator.Expression.ToString().SubString(1, $Enumerator.Expression.ToString().Length - 2))
                }

                [void]$PropertyArray.Add($Hashtable)
            }

            $CsvObjectParameters = @{
                'Property' = $PropertyArray
                'Unique' = $Configuration.InputObject.Unique
            }

            $SqlDataAdapterCommandSet = $Configuration.SqlDataAdapterCommandSet

            if ($SqlDataAdapterCommandSet.Keys -NotContains 'Insert' -and $SqlDataAdapterCommandSet.Keys -NotContains 'Update') {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New("Configuration $DataTemplateName not implemented for import."),
                    '1',
                    [System.Management.Automation.ErrorCategory]::NotImplemented,
                    $DataTemplateName
                )
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                $SqlServerInstanceParameters = @{
                    'ServerInstance' = $ServerInstance
                    'DatabaseName' = 'master'
                    'Verbose' = [bool]($PSBoundParameters['Verbose'])
                }

                $SqlConnection = Connect-SqlServerInstance @SqlServerInstanceParameters
            }

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Import SQL Data.'
                'Status' = [string]::Format('Step {0} of {1}', 0, 3)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }

            $ChildProgressParameters = @{
                'Id' = 1
                'ParentId' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:\SqlConnection) {
                    $SqlConnection.Dispose()
                }
            }

            throw $_
        }
    }

    process {
        try {
            #Region Reading Destination Dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 1, 3)
            $ProgressParameters.CurrentOperation = 'Reading destination dataset.'
            $ProgressParameters.PercentComplete = 0 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $SqlClientDataSetParameters = @{
                'SqlConnection' = $SqlConnection
                'SqlCommand' = $SqlDataAdapterCommandSet.Select.CommandText
                'DataSetName' = 'SqlDataTools'
                'DataTableName' = $DataSetTableName
                'OutputAs' = 'DataSet'
            }

            $DataSet = Get-SqlClientDataSet @SqlClientDataSetParameters

            $KeyColumns = [System.Collections.Generic.List[System.Data.DataColumn]]::New()

            foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                $KeyColumns.Add($DataSet.Tables[$DataSetTableName].Columns[$PrimaryKey])
            }

            try {
                $DataSet.Tables[$DataSetTableName].PrimaryKey = $KeyColumns
            }
            catch {
                $ErrorMessage = [string]::Format("These columns do not have unique values in table {0}: {1}", $DataSetTableName, [string]::Join(', ', $KeyColumns.ColumnName))

                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New($ErrorMessage),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidData,
                    $DataSetTableName
                )
            }
            #Endregion

            #Region Add CSV data to dataset
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 2, 3)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 1 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes, $Configuration.InputColumns.Name)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes, $Configuration.InputColumns.Name)
            }

            $Results = [SqlClient.DataSetResult]::New()

            if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                $UnmodifiedList = [System.Collections.Generic.List[psobject]]::New()
            }

            try {
                $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                Write-Verbose $ChildProgressParameters.CurrentOperation
                Write-Progress @ChildProgressParameters

                while ($Row = $TextFieldParser.ReadLine()) {
                    if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                        $ChildProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                        $ChildProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                        $ChildProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                        Write-Verbose $ChildProgressParameters.CurrentOperation
                        Write-Progress @ChildProgressParameters
                    }

                    if ($SkipValidation -or $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                        $TransformedRow = $Row | Select-Object @CsvObjectParameters

                        $PrimaryKeyValuesList = [System.Collections.Generic.List[String]]::New()

                        foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                            $PrimaryKeyValuesList.Add($TransformedRow.$PrimaryKey)
                        }

                        $PrimaryKeyValues = $($PrimaryKeyValuesList -as [string[]])

                        if ($DataSet.Tables[$DataSetTableName].Rows.Contains($PrimaryKeyValues)) {
                            $DataRow = $DataSet.Tables[$DataSetTableName].Rows.Find($PrimaryKeyValues)

                            foreach ($Property in $TransformedRow.PsObject.Properties) {
                                if ($Property.Name -NotIn $Configuration.DataTable.PrimaryKeys) {
                                    if ($null -eq $Property.Value) {
                                        if ($DataRow.Item($Property.Name) -ne [DBNull]::Value) {
                                            $DataRow.Item($Property.Name) = [DBNull]::Value
                                        }
                                    } else {
                                        if ($DataRow.Item($Property.Name) -ne $Property.Value) {
                                            $DataRow.Item($Property.Name) = $Property.Value
                                        }
                                    }
                                }
                            }

                            if ($DataRow.RowState -eq 'Unchanged') {
                                if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                                    $UnmodifiedList.Add($($DataRow | Select-Object -Property $Configuration.DataTable.PrimaryKeys))
                                }

                                $Results.UnchangedRows++
                            } else {
                                $Results.UpdatedRows++
                            }
                        } else {
                            $DataRow = $DataSet.Tables[$DataSetTableName].NewRow()

                            foreach ($Property in $TransformedRow.PsObject.Properties) {
                                if ($null -eq $Property.Value) {
                                    $DataRow.Item($Property.Name) = [DBNull]::Value
                                } else {
                                    $DataRow.Item($Property.Name) = $Property.Value
                                }
                            }

                            [void]$DataSet.Tables[$DataSetTableName].Rows.Add($DataRow)

                            $Results.InsertedRows++
                        }
                    }
                }
            }
            catch {
                throw $_
            }
            finally {
                Write-Progress -Id 1 -Activity 'Reading delimited file.' -Completed
            }
            #Endregion

            #Region Delete Rows from dataset
            if ($SqlDataAdapterCommandSet.Keys -Contains 'Delete') {
                $UnchangedRows = $DataSet.Tables[$DataSetTableName].Rows.where({$_.RowState -eq 'Unchanged'})

                if ($UnchangedRows.Count -gt $UnmodifiedList.Count) {
                    $Difference = Compare-Object -ReferenceObject $($UnchangedRows | Select-Object $Configuration.DataTable.PrimaryKeys) -DifferenceObject $UnmodifiedList

                    foreach ($Item in $Difference) {
                        if ($Item.SideIndicator -eq '<=') {
                            $PrimaryKeyValuesList = [System.Collections.Generic.List[String]]::New()

                            foreach ($PrimaryKey in $Configuration.DataTable.PrimaryKeys) {
                                $PrimaryKeyValuesList.Add($Item.InputObject.$PrimaryKey)
                            }

                            $DataSet.Tables[$DataSetTableName].Rows.Find($($PrimaryKeyValuesList -as [string[]])).Delete()

                            $Results.UnchangedRows--
                            $Results.DeletedRows++
                        }
                    }
                }

                Remove-Variable -Name UnmodifiedList
            }
            #EndRegion

            #Region Set Sql Data Adapter
            $ProgressParameters.Activity = 'Import SQL Data.'
            $ProgressParameters.Status = [string]::Format('Step {0} of {1}', 3, 3)
            $ProgressParameters.CurrentOperation = 'Adding CSV data to dataset.'
            $ProgressParameters.PercentComplete = 2 / 3 * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            $TotalChangedRows = $Results.InsertedRows + $Results.UpdatedRows + $Results.DeletedRows

            if ($TotalChangedRows -gt 0) {
                $SqlDataAdapter = [Microsoft.Data.SqlClient.SqlDataAdapter]::New()

                $SqlDataAdapter.UpdateBatchSize = $BatchSize

                foreach ($Key in $SqlDataAdapterCommandSet.Keys) {
                    $SqlCommand = $SqlDataAdapterCommandSet.$Key

                    $SqlCommandObject = [Microsoft.Data.SqlClient.SqlCommand]::New($SqlCommand.CommandText, $SqlConnection)

                    if ($Key -ne 'Select') {
                        $SqlCommandObject.UpdatedRowSource = [System.Data.UpdateRowSource]::None
                    }

                    if ($SqlCommand.Keys -contains 'Parameters') {
                        foreach ($Parameter in $SqlCommand.Parameters) {
                            [void]$SqlCommandObject.Parameters.Add($Parameter.ParameterName, [System.Data.SqlDbType]$Parameter.SqlDbType)

                            switch ($Parameter.keys) {
                                {$_ -contains 'Precision'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Precision = $Parameter.Precision
                                }
                                {$_ -contains 'Scale'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Scale = $Parameter.Scale
                                }
                                {$_ -contains 'Size'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].Size = $Parameter.Size
                                }
                                {$_ -contains 'SourceColumn'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].SourceColumn = $Parameter.SourceColumn
                                }
                                {$_ -contains 'SourceVersion'} {
                                    $SqlCommandObject.Parameters[$Parameter.ParameterName].SourceVersion = [System.Data.DataRowVersion]$Parameter.SourceVersion
                                }
                                Default {}
                            }
                        }
                    }

                    switch ($Key) {
                        'Select' {
                            $SqlDataAdapter.SelectCommand = $SqlCommandObject
                        }
                        'Insert' {
                            $SqlDataAdapter.InsertCommand = $SqlCommandObject
                        }
                        'Update' {
                            $SqlDataAdapter.UpdateCommand = $SqlCommandObject
                        }
                        'Delete' {
                            $SqlDataAdapter.DeleteCommand = $SqlCommandObject
                        }
                    }
                }

                if ($PSCmdlet.ShouldProcess($DatabaseName, "Update $DataTemplateName")) {
                    $SqlClientDataSetParameters = @{
                        'DataSet' = $DataSet
                        'DataSetTableName' = $DataSetTableName
                        'SqlDataAdapter' = $SqlDataAdapter
                        'Confirm' = $false
                    }

                    $Results = Save-SqlClientDataSet @SqlClientDataSetParameters
                }
            }
            #Endregion

            $Results
        }
        catch {
            throw $_
        }
        finally{
            Write-Progress -Id 1 -Activity 'Import SQL Data.' -Completed
            Write-Progress -Id 0 -Activity 'Import SQL Data.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            if (Test-Path -Path Variable:SqlDataAdapter) {
                $SqlDataAdapter.Dispose()
            }

            if (Test-Path -Path Variable:SqlCommandObject) {
                $SqlCommandObject.Dispose()
            }

            if ($PSCmdlet.ParameterSetName -in $ServerInstanceParameterSets) {
                if (Test-Path -Path Variable:SqlConnection) {
                    Disconnect-SqlServerInstance -SqlConnection $SqlConnection
                }
            }
        }
    }

    end {
    }
}

function Test-SqlData {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([System.Boolean])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 0
        )]
        [Mode]$Mode,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 1
        )]
        [ArgumentCompleter({ [ArgumentCompleterResult]::GetArgumentCompleterResult($Args) })]
        [string]$DataTemplateName,

        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            Position = 2
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$Path,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [Encoding]$Encoding,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [char]$Delimiter,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$IgnoreQuotes
    )

    begin {
        try {
            if ($Mode -eq 'Export') {
                throw [System.Management.Automation.ErrorRecord]::New(
                    [Exception]::New('Export mode not valid for test.'),
                    '1',
                    [System.Management.Automation.ErrorCategory]::InvalidArgument,
                    $Mode
                )
            }

            $Configuration = $Script:PSData."$Mode"."$DataTemplateName"

            $ProgressParameters = @{
                'Id' = 0
                'Activity' = 'Reading delimited file.'
                'Status' = [string]::Format('Line {0} of {1}', 0, 1)
                'CurrentOperation' = ''
                'PercentComplete' = 0
            }
        }
        catch {
            throw
        }
    }

    process {
        try {
            if ($PSBoundParameters.ContainsKey('Delimiter')) {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, $Delimiter, $IgnoreQuotes, $Configuration.InputColumns.Name)
            } else {
                $TextFieldParser = [TextFieldParser]::New($Path, $Encoding, ',', $IgnoreQuotes, $Configuration.InputColumns.Name)
            }

            $ReturnValue = $true

            $ProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
            $ProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
            $ProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
            $ProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

            Write-Verbose $ProgressParameters.CurrentOperation
            Write-Progress @ProgressParameters

            while ($Row = $TextFieldParser.ReadLine()) {
                if ($TextFieldParser.TotalLines -lt 10000 -or $TextFieldParser.CurrentLine % [System.Math]::Floor($TextFieldParser.TotalLines / 10000) -eq 0) {
                    $ProgressParameters.Activity = [string]::Format('Line Number: {0}', $TextFieldParser.CurrentLine)
                    $ProgressParameters.Status = [string]::Format('Line {0} of {1}', $TextFieldParser.CurrentLine, $TextFieldParser.TotalLines)
                    $ProgressParameters.CurrentOperation = [string]::Format('Reading line: {0}', $TextFieldParser.CurrentLine)
                    $ProgressParameters.PercentComplete = $TextFieldParser.CurrentLine / $TextFieldParser.TotalLines * 100

                    Write-Verbose $ProgressParameters.CurrentOperation
                    Write-Progress @ProgressParameters
                }

                if (-not $TextFieldParser.IsValidRow($Row, $Configuration.InputColumns)) {
                    $ReturnValue = $false
                }
            }
        }
        catch {
            $ReturnValue = $false

            $PSCmdlet.WriteError($_)
        }
        finally {
            Write-Progress -Id 0 -Activity 'Reading delimited file.' -Completed

            if (Test-Path -Path Variable:TextFieldParser) {
                $TextFieldParser.Close()
            }

            $ReturnValue
        }
    }

    end {
    }
}

function Use-TemplateRepository {
    <#
    .EXTERNALHELP
    SqlDataTools-Help.xml
    #>

    [System.Diagnostics.DebuggerStepThrough()]

    [CmdletBinding(
        PositionalBinding = $false,
        SupportsShouldProcess = $false,
        ConfirmImpact = 'Low'
    )]

    [OutputType([System.Void])]

    param (
        [Parameter(
            Mandatory = $true,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [ValidatePathExists('Leaf')]
        [System.IO.FileInfo][TransformPath()]$DataTemplatePath,

        [Parameter(
            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false
        )]
        [switch]$SkipSignatureValidation
    )

    begin {

    }

    process {
        try {
            if (-not $SkipSignatureValidation) {
                $AuthenticodeSignature = Get-AuthenticodeSignature -FilePath $DataTemplatePath

                if ($AuthenticodeSignature.Status -ne 'Valid') {
                    throw [System.Management.Automation.ErrorRecord]::New(
                        [Exception]::New('Invalid signature file on data file.'),
                        '1',
                        [System.Management.Automation.ErrorCategory]::ResourceExists,
                        $DataTemplatePath
                    )
                }
            }

            $PowerShellDataFileParameters = @{
                'LiteralPath' = $DataTemplatePath
            }

            if ($PSVersionTable.PSVersion -ge [version]'7.2') {
                $PowerShellDataFileParameters.Add('SkipLimitCheck', $true)
            }

            # $Script:PSData.Add($(Import-PowerShellDataFile @PowerShellDataFileParameters))
            $Script:PSData = Import-PowerShellDataFile @PowerShellDataFileParameters
        }
        catch {
            throw $_
        }
    }

    end {
    }
}

# SIG # Begin signature block
# MIInywYJKoZIhvcNAQcCoIInvDCCJ7gCAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDcEblfSYf0dU/0
# qn5kuywlH0uwFkFwEpoovT2o0uZxOKCCINswggWNMIIEdaADAgECAhAOmxiO+dAt
# 5+/bUOIIQBhaMA0GCSqGSIb3DQEBDAUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQK
# EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNV
# BAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0yMjA4MDEwMDAwMDBa
# Fw0zMTExMDkyMzU5NTlaMGIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2Vy
# dCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xITAfBgNVBAMTGERpZ2lD
# ZXJ0IFRydXN0ZWQgUm9vdCBHNDCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoC
# ggIBAL/mkHNo3rvkXUo8MCIwaTPswqclLskhPfKK2FnC4SmnPVirdprNrnsbhA3E
# MB/zG6Q4FutWxpdtHauyefLKEdLkX9YFPFIPUh/GnhWlfr6fqVcWWVVyr2iTcMKy
# unWZanMylNEQRBAu34LzB4TmdDttceItDBvuINXJIB1jKS3O7F5OyJP4IWGbNOsF
# xl7sWxq868nPzaw0QF+xembud8hIqGZXV59UWI4MK7dPpzDZVu7Ke13jrclPXuU1
# 5zHL2pNe3I6PgNq2kZhAkHnDeMe2scS1ahg4AxCN2NQ3pC4FfYj1gj4QkXCrVYJB
# MtfbBHMqbpEBfCFM1LyuGwN1XXhm2ToxRJozQL8I11pJpMLmqaBn3aQnvKFPObUR
# WBf3JFxGj2T3wWmIdph2PVldQnaHiZdpekjw4KISG2aadMreSx7nDmOu5tTvkpI6
# nj3cAORFJYm2mkQZK37AlLTSYW3rM9nF30sEAMx9HJXDj/chsrIRt7t/8tWMcCxB
# YKqxYxhElRp2Yn72gLD76GSmM9GJB+G9t+ZDpBi4pncB4Q+UDCEdslQpJYls5Q5S
# UUd0viastkF13nqsX40/ybzTQRESW+UQUOsxxcpyFiIJ33xMdT9j7CFfxCBRa2+x
# q4aLT8LWRV+dIPyhHsXAj6KxfgommfXkaS+YHS312amyHeUbAgMBAAGjggE6MIIB
# NjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdDgQWBBTs1+OC0nFdZEzfLmc/57qYrhwP
# TzAfBgNVHSMEGDAWgBRF66Kv9JLLgjEtUYunpyGd823IDzAOBgNVHQ8BAf8EBAMC
# AYYweQYIKwYBBQUHAQEEbTBrMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5kaWdp
# Y2VydC5jb20wQwYIKwYBBQUHMAKGN2h0dHA6Ly9jYWNlcnRzLmRpZ2ljZXJ0LmNv
# bS9EaWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcnQwRQYDVR0fBD4wPDA6oDigNoY0
# aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENB
# LmNybDARBgNVHSAECjAIMAYGBFUdIAAwDQYJKoZIhvcNAQEMBQADggEBAHCgv0Nc
# Vec4X6CjdBs9thbX979XB72arKGHLOyFXqkauyL4hxppVCLtpIh3bb0aFPQTSnov
# Lbc47/T/gLn4offyct4kvFIDyE7QKt76LVbP+fT3rDB6mouyXtTP0UNEm0Mh65Zy
# oUi0mcudT6cGAxN3J0TU53/oWajwvy8LpunyNDzs9wPHh6jSTEAZNUZqaVSwuKFW
# juyk1T3osdz9HNj0d1pcVIxv76FQPfx2CWiEn2/K2yCNNWAcAgPLILCsWKAOQGPF
# mCLBsln1VWvPJ6tsds5vIy30fnFqI2si/xK4VC0nftg62fC2h5b9W9FcrBjDTZ9z
# twGpn1eqXijiuZQwgga0MIIEnKADAgECAhANx6xXBf8hmS5AQyIMOkmGMA0GCSqG
# SIb3DQEBCwUAMGIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMx
# GTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xITAfBgNVBAMTGERpZ2lDZXJ0IFRy
# dXN0ZWQgUm9vdCBHNDAeFw0yNTA1MDcwMDAwMDBaFw0zODAxMTQyMzU5NTlaMGkx
# CzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5EaWdpQ2VydCwgSW5jLjFBMD8GA1UEAxM4
# RGlnaUNlcnQgVHJ1c3RlZCBHNCBUaW1lU3RhbXBpbmcgUlNBNDA5NiBTSEEyNTYg
# MjAyNSBDQTEwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQC0eDHTCphB
# cr48RsAcrHXbo0ZodLRRF51NrY0NlLWZloMsVO1DahGPNRcybEKq+RuwOnPhof6p
# vF4uGjwjqNjfEvUi6wuim5bap+0lgloM2zX4kftn5B1IpYzTqpyFQ/4Bt0mAxAHe
# HYNnQxqXmRinvuNgxVBdJkf77S2uPoCj7GH8BLuxBG5AvftBdsOECS1UkxBvMgEd
# gkFiDNYiOTx4OtiFcMSkqTtF2hfQz3zQSku2Ws3IfDReb6e3mmdglTcaarps0wjU
# jsZvkgFkriK9tUKJm/s80FiocSk1VYLZlDwFt+cVFBURJg6zMUjZa/zbCclF83bR
# VFLeGkuAhHiGPMvSGmhgaTzVyhYn4p0+8y9oHRaQT/aofEnS5xLrfxnGpTXiUOeS
# LsJygoLPp66bkDX1ZlAeSpQl92QOMeRxykvq6gbylsXQskBBBnGy3tW/AMOMCZIV
# NSaz7BX8VtYGqLt9MmeOreGPRdtBx3yGOP+rx3rKWDEJlIqLXvJWnY0v5ydPpOjL
# 6s36czwzsucuoKs7Yk/ehb//Wx+5kMqIMRvUBDx6z1ev+7psNOdgJMoiwOrUG2Zd
# SoQbU2rMkpLiQ6bGRinZbI4OLu9BMIFm1UUl9VnePs6BaaeEWvjJSjNm2qA+sdFU
# eEY0qVjPKOWug/G6X5uAiynM7Bu2ayBjUwIDAQABo4IBXTCCAVkwEgYDVR0TAQH/
# BAgwBgEB/wIBADAdBgNVHQ4EFgQU729TSunkBnx6yuKQVvYv1Ensy04wHwYDVR0j
# BBgwFoAU7NfjgtJxXWRM3y5nP+e6mK4cD08wDgYDVR0PAQH/BAQDAgGGMBMGA1Ud
# JQQMMAoGCCsGAQUFBwMIMHcGCCsGAQUFBwEBBGswaTAkBggrBgEFBQcwAYYYaHR0
# cDovL29jc3AuZGlnaWNlcnQuY29tMEEGCCsGAQUFBzAChjVodHRwOi8vY2FjZXJ0
# cy5kaWdpY2VydC5jb20vRGlnaUNlcnRUcnVzdGVkUm9vdEc0LmNydDBDBgNVHR8E
# PDA6MDigNqA0hjJodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRUcnVz
# dGVkUm9vdEc0LmNybDAgBgNVHSAEGTAXMAgGBmeBDAEEAjALBglghkgBhv1sBwEw
# DQYJKoZIhvcNAQELBQADggIBABfO+xaAHP4HPRF2cTC9vgvItTSmf83Qh8WIGjB/
# T8ObXAZz8OjuhUxjaaFdleMM0lBryPTQM2qEJPe36zwbSI/mS83afsl3YTj+IQhQ
# E7jU/kXjjytJgnn0hvrV6hqWGd3rLAUt6vJy9lMDPjTLxLgXf9r5nWMQwr8Myb9r
# EVKChHyfpzee5kH0F8HABBgr0UdqirZ7bowe9Vj2AIMD8liyrukZ2iA/wdG2th9y
# 1IsA0QF8dTXqvcnTmpfeQh35k5zOCPmSNq1UH410ANVko43+Cdmu4y81hjajV/gx
# dEkMx1NKU4uHQcKfZxAvBAKqMVuqte69M9J6A47OvgRaPs+2ykgcGV00TYr2Lr3t
# y9qIijanrUR3anzEwlvzZiiyfTPjLbnFRsjsYg39OlV8cipDoq7+qNNjqFzeGxcy
# tL5TTLL4ZaoBdqbhOhZ3ZRDUphPvSRmMThi0vw9vODRzW6AxnJll38F0cuJG7uEB
# YTptMSbhdhGQDpOXgpIUsWTjd6xpR6oaQf/DJbg3s6KCLPAlZ66RzIg9sC+NJpud
# /v4+7RWsWCiKi9EOLLHfMR2ZyJ/+xhCx9yHbxtl5TPau1j/1MIDpMPx0LckTetiS
# uEtQvLsNz3Qbp7wGWqbIiOWCnb5WqxL3/BAPvIXKUjPSxyZsq8WhbaM2tszWkPZP
# ubdcMIIGuTCCBKGgAwIBAgIRAJmjgAomVTtlq9xuhKaz6jkwDQYJKoZIhvcNAQEM
# BQAwgYAxCzAJBgNVBAYTAlBMMSIwIAYDVQQKExlVbml6ZXRvIFRlY2hub2xvZ2ll
# cyBTLkEuMScwJQYDVQQLEx5DZXJ0dW0gQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkx
# JDAiBgNVBAMTG0NlcnR1bSBUcnVzdGVkIE5ldHdvcmsgQ0EgMjAeFw0yMTA1MTkw
# NTMyMThaFw0zNjA1MTgwNTMyMThaMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhB
# c3NlY28gRGF0YSBTeXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNp
# Z25pbmcgMjAyMSBDQTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAJ0j
# zwQwIzvBRiznM3M+Y116dbq+XE26vest+L7k5n5TeJkgH4Cyk74IL9uP61olRsxs
# U/WBAElTMNQI/HsE0uCJ3VPLO1UufnY0qDHG7yCnJOvoSNbIbMpT+Cci75scCx7U
# sKK1fcJo4TXetu4du2vEXa09Tx/bndCBfp47zJNsamzUyD7J1rcNxOw5g6FJg0Im
# Iv7nCeNn3B6gZG28WAwe0mDqLrvU49chyKIc7gvCjan3GH+2eP4mYJASflBTQ3HO
# s6JGdriSMVoD1lzBJobtYDF4L/GhlLEXWgrVQ9m0pW37KuwYqpY42grp/kSYE4BU
# QrbLgBMNKRvfhQPskDfZ/5GbTCyvlqPN+0OEDmYGKlVkOMenDO/xtMrMINRJS5SY
# +jWCi8PRHAVxO0xdx8m2bWL4/ZQ1dp0/JhUpHEpABMc3eKax8GI1F03mSJVV6o/n
# mmKqDE6TK34eTAgDiBuZJzeEPyR7rq30yOVw2DvetlmWssewAhX+cnSaaBKMEj9O
# 2GgYkPJ16Q5Da1APYO6n/6wpCm1qUOW6Ln1J6tVImDyAB5Xs3+JriasaiJ7P5KpX
# eiVV/HIsW3ej85A6cGaOEpQA2gotiUqZSkoQUjQ9+hPxDVb/Lqz0tMjp6RuLSKAR
# sVQgETwoNQZ8jCeKwSQHDkpwFndfCceZ/OfCUqjxAgMBAAGjggFVMIIBUTAPBgNV
# HRMBAf8EBTADAQH/MB0GA1UdDgQWBBTddF1MANt7n6B0yrFu9zzAMsBwzTAfBgNV
# HSMEGDAWgBS2oVQ5AsOgP46KvPrU+Bym0ToO/TAOBgNVHQ8BAf8EBAMCAQYwEwYD
# VR0lBAwwCgYIKwYBBQUHAwMwMAYDVR0fBCkwJzAloCOgIYYfaHR0cDovL2NybC5j
# ZXJ0dW0ucGwvY3RuY2EyLmNybDBsBggrBgEFBQcBAQRgMF4wKAYIKwYBBQUHMAGG
# HGh0dHA6Ly9zdWJjYS5vY3NwLWNlcnR1bS5jb20wMgYIKwYBBQUHMAKGJmh0dHA6
# Ly9yZXBvc2l0b3J5LmNlcnR1bS5wbC9jdG5jYTIuY2VyMDkGA1UdIAQyMDAwLgYE
# VR0gADAmMCQGCCsGAQUFBwIBFhhodHRwOi8vd3d3LmNlcnR1bS5wbC9DUFMwDQYJ
# KoZIhvcNAQEMBQADggIBAHWIWA/lj1AomlOfEOxD/PQ7bcmahmJ9l0Q4SZC+j/v0
# 9CD2csX8Yl7pmJQETIMEcy0VErSZePdC/eAvSxhd7488x/Cat4ke+AUZZDtfCd8y
# HZgikGuS8mePCHyAiU2VSXgoQ1MrkMuqxg8S1FALDtHqnizYS1bIMOv8znyJjZQE
# Sp9RT+6NH024/IqTRsRwSLrYkbFq4VjNn/KV3Xd8dpmyQiirZdrONoPSlCRxCIi5
# 4vQcqKiFLpeBm5S0IoDtLoIe21kSw5tAnWPazS6sgN2oXvFpcVVpMcq0C4x/CLSN
# e0XckmmGsl9z4UUguAJtf+5gE8GVsEg/ge3jHGTYaZ/MyfujE8hOmKBAUkVa7NMx
# RSB1EdPFpNIpEn/pSHuSL+kWN/2xQBJaDFPr1AX0qLgkXmcEi6PFnaw5T17UdIIn
# A58rTu3mefNuzUtse4AgYmxEmJDodf8NbVcU6VdjWtz0e58WFZT7tST6EWQmx/Oo
# HPelE77lojq7lpsjhDCzhhp4kfsfszxf9g2hoCtltXhCX6NqsqwTT7xe8LgMkH4h
# Vy8L1h2pqGLT2aNCx7h/F95/QvsTeGGjY7dssMzq/rSshFQKLZ8lPb8hFTmiGDJN
# yHga5hZ59IGynk08mHhBFM/0MLeBzlAQq1utNjQprztZ5vv/NJy8ua9AGbwkMWkO
# MIIG4DCCBMigAwIBAgIQQ7s0QZ8qUnHOP66HyvajHjANBgkqhkiG9w0BAQsFADBW
# MQswCQYDVQQGEwJQTDEhMB8GA1UEChMYQXNzZWNvIERhdGEgU3lzdGVtcyBTLkEu
# MSQwIgYDVQQDExtDZXJ0dW0gQ29kZSBTaWduaW5nIDIwMjEgQ0EwHhcNMjYwOTEy
# MjEzNTQ3WhcNMjcwOTEyMjEzNTQ2WjCBhTELMAkGA1UEBhMCVVMxFzAVBgNVBAgM
# DlNvdXRoIENhcm9saW5hMREwDwYDVQQHDAhOZXdiZXJyeTEeMBwGA1UECgwVT3Bl
# biBTb3VyY2UgRGV2ZWxvcGVyMSowKAYDVQQDDCFPcGVuIFNvdXJjZSBEZXZlbG9w
# ZXIgUm9iZXJ0IEVkZXIwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQC6
# 72ybkWUB620cgb49nkhI4VvtWKABeSD8277f0Jww+/5fKUxoX2vC77QjmaSXU+I9
# LB2YTj8yCSTDVlxIwJoS7KgsIN9P2EC6ehJe9FRI0n2Rt33/VlRY8kuSh2XsIVGg
# i6Y3RooWVjVmYCAOXuV5zrqjGY1P72nrSIekxcGCTo1Y0nmRwsECCIYn7b3ZM065
# u9b3AtYf3oI4grblWhtY0kbuHNCSeDjpp+qExQgeIs6OpFACJSDASFiDnF8+L+bB
# V+UtUcFbvlu0WpQyblXw9vdN7BEIdqZ/1fxyhjHxqpSuOoTqoZcyDjilXtnMOfWp
# gfgdKPFRc6NHwrmxUyNLAYHOsqBC+bMxamurB1qCJ/16lFbW/YWJRrJsaeA2WaPw
# 8ulkUnZUoP9JgyVE1nwZbhZOgE3YwlVLmBBsAKsRiyJWBYqG1VdaMpfzLYJVNTc8
# 4F/90uC2BvIoafcFfNyc8dIpdd7Ni17JmYuD0+/u1gqUi3p+Qdlk/y+Vgsb1x+0z
# kaMA6CzjFA83szdzwRLFDDnaUOVngyRR8+JLnSNuEw9nNM3G5WWRGBDOkLbwq+NO
# H1gWWAaJJYOlTARrg7ea2JHLl66CBJaaledp2u8hBl1Y5yQTpIkVYx0VPt1Oa7Nq
# kOkXlNjmmlYsZm5pe3fSTBPx5YdUOeIl1sAWXGH/+QIDAQABo4IBeDCCAXQwDAYD
# VR0TAQH/BAIwADA9BgNVHR8ENjA0MDKgMKAuhixodHRwOi8vY2NzY2EyMDIxLmNy
# bC5jZXJ0dW0ucGwvY2NzY2EyMDIxLmNybDBzBggrBgEFBQcBAQRnMGUwLAYIKwYB
# BQUHMAGGIGh0dHA6Ly9jY3NjYTIwMjEub2NzcC1jZXJ0dW0uY29tMDUGCCsGAQUF
# BzAChilodHRwOi8vcmVwb3NpdG9yeS5jZXJ0dW0ucGwvY2NzY2EyMDIxLmNlcjAf
# BgNVHSMEGDAWgBTddF1MANt7n6B0yrFu9zzAMsBwzTAdBgNVHQ4EFgQUCdHvF5Qw
# xL78QY6vfJNPC89uTWEwSwYDVR0gBEQwQjAIBgZngQwBBAEwNgYLKoRoAYb2dwIF
# AQQwJzAlBggrBgEFBQcCARYZaHR0cHM6Ly93d3cuY2VydHVtLnBsL0NQUzATBgNV
# HSUEDDAKBggrBgEFBQcDAzAOBgNVHQ8BAf8EBAMCB4AwDQYJKoZIhvcNAQELBQAD
# ggIBADGvhUwApFlOiMwxr4muEgH+EK8xGwyw7qZGQZYdHQZV7E6ev+4i0u2ywMbF
# H0XcYpaB4xubprYIGbltJhIXoIM+BmIB6mgzgAtKMJBIWMECnACZKPAWPJ5vp3Xu
# GLCg0gwQGZEKJInwEFLzplH3G5g8hTO8KSLmWLVWoWHTTA3WI4LgTf/XRs3QYqur
# bB1gWRWa+vx8J/4I6znbpnpRDxy/jCYh9qtv21Dk3BovIPnfaj50JOWJhWeongQ6
# Dgd4/FZhM/U1Fj/g1W7WDMal9q43MABwmrHPbxrIEK1V5vXwAhK1m9eSaZ8bqbeA
# SId0wOYzIyEziquoO5TCdf/lSi8nD4BIm2E+h738pLQXWvr6tYWyvqaUN0uk5f27
# NsXlVRYp8EUZPP83BJMaQJFgTsYPMeZejAndk3nuqPVGeCL6WW90M7eK5sPbTAmW
# WrSnYFx4pgnMR3X3s14074ytJ3o3ycKa0bxjhMcoCTfMDmV7jUUhATpW8iZ2/E4b
# +0s7DmbN37VsBngsj04vMyVxhcNLSwdFDTQEgkEwHccChlw0anfoZJ7Xui4x5RSr
# j3rOyyrf7mFYIpsbDYjVxBo5/JVJuu5h2+BRxY9MLoSMknm391tCI7aVC/XTl/zW
# rX1kJeCx0nYBnZnmjWCRrCXWOX5V8QaLAnK/R6durGeXnlo3MIIG7TCCBNWgAwIB
# AgIQCE/cM09+RU7bww+P+ZIYNTANBgkqhkiG9w0BAQsFADBpMQswCQYDVQQGEwJV
# UzEXMBUGA1UEChMORGlnaUNlcnQsIEluYy4xQTA/BgNVBAMTOERpZ2lDZXJ0IFRy
# dXN0ZWQgRzQgVGltZVN0YW1waW5nIFJTQTQwOTYgU0hBMjU2IDIwMjUgQ0ExMB4X
# DTI2MDgwNTAwMDAwMFoXDTM3MTEwNDIzNTk1OVowYzELMAkGA1UEBhMCVVMxFzAV
# BgNVBAoTDkRpZ2lDZXJ0LCBJbmMuMTswOQYDVQQDEzJEaWdpQ2VydCBTSEEyNTYg
# UlNBNDA5NiBUaW1lc3RhbXAgUmVzcG9uZGVyIDIwMjYgMTCCAiIwDQYJKoZIhvcN
# AQEBBQADggIPADCCAgoCggIBALZ7pvLJ/s1K+NSbTGWz/TjGMPh8CQ6RucZCLv5a
# nHzWJjF/NWJrFIhy24fcpKXlgRiky4WAawDfU3YP0BMxt9l3Dm5oCG5Z69AqEN1k
# gHg2epx+l+lZBcmJCcN0ASURML5uFIS80sZsDwO3BSkUxDjLJhBI+qiZP3aixAC/
# qEGLjsBNlLol9VZ7pfGEXiMlneJIC5/YKuizVzNFKZZEeoy/0B8Zm+nzKBgSWG52
# lCO1w+nCg6XpCtklTJXeIg283hw7TmmsZXR+SMbjbrEOvZ3fP2VxIgeR28Y90ZSt
# d3F9VuA5RVynb/whITPAo9b75Zr4Ta6Mj3URm26QZYMn/FnbuTegcoRcFEZ9FOqM
# 5T6MTdtr/n74lIT/ug0eeOzmZ6QTFg33otX+bFRsIolvykE1jive4PuESaT8zzVe
# FWDAMDtozNgLctkGD1ZjkEyZtJrLl5ya0m5doH/ScpaZCZVl6pNUOCybMc/kxC6E
# AmSJY24L0yYKD1Nkddsnb/ItVKi/2nXpQNMu1PT5prW83vV8d67WowuUs0HdY4H8
# AMLGvdL/WHEj3ZnqMqAQQP9u3Ai9t+5eQ02GDwy0ODjdzi0xlp70W+ow63/0++YD
# EX1M0iwgUHwbrJvfpklkZQvw3+kv3vUPItdwroczk9icflf55W1zOEKAcJVAIXpc
# MCU9AgMBAAGjggGVMIIBkTAMBgNVHRMBAf8EAjAAMB0GA1UdDgQWBBQUyWOKMC7U
# SvtulPPm40B+9ezN4jAfBgNVHSMEGDAWgBTvb1NK6eQGfHrK4pBW9i/USezLTjAO
# BgNVHQ8BAf8EBAMCB4AwFgYDVR0lAQH/BAwwCgYIKwYBBQUHAwgwgZUGCCsGAQUF
# BwEBBIGIMIGFMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5kaWdpY2VydC5jb20w
# XQYIKwYBBQUHMAKGUWh0dHA6Ly9jYWNlcnRzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2Vy
# dFRydXN0ZWRHNFRpbWVTdGFtcGluZ1JTQTQwOTZTSEEyNTYyMDI1Q0ExLmNydDBf
# BgNVHR8EWDBWMFSgUqBQhk5odHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNl
# cnRUcnVzdGVkRzRUaW1lU3RhbXBpbmdSU0E0MDk2U0hBMjU2MjAyNUNBMS5jcmww
# IAYDVR0gBBkwFzAIBgZngQwBBAIwCwYJYIZIAYb9bAcBMA0GCSqGSIb3DQEBCwUA
# A4ICAQCNxTphHp1SCt+ZrAmAfn0oQLFr0mLywSLaDXQIENoyKqxrFbJblzCVP/pk
# XmwXOdrOpWygLzlT12os5ipDCy35RBCg2UMeApEtrfGhz45F4Wt4WGdNdIbRWt3Y
# TYJmpR+b7lr4d7Uwn+H600u4D7RnOGf8Wj4UNgAdZkfHhHv1mx9EVh71SJelcEN/
# oORSjXzdjfw1iZH9d8Nh/thn6hH23d+VsPAr6GAYyzSA02nXD1nYLI7Ijmiv+xLC
# iYC41DSFYL3GhTiy0PxpawPtGRyaBVGzq+UiTfM8pD7KVyF5aQyWP4KhVGUUTnmm
# /RlYJoW3TiXA/+t0YcT2oRVBm3JETjajHug2AL+v5jhtKVnd3D0rbHXEu27o+Q8p
# 4sEWPMqKDB+qbceb6T/6WcwTwXmQ9lOCLLYcsQeSWmvKqzpAec9etE14jOQAzLKW
# dE3w/TCaKtLRaRT7LCkRYVnhA2D73FLje1O5b3HR5eHs0NzU/+xX7NbEdcofy0W3
# Wdwd1XOqtlpg/JgwtKfZM5dqO94lbUveOiJBI+xZEbGRsMNbXmMREUTgu+Oca7Y7
# 3MPWcslIx2VhkSKSXjDbD6rgg39H5Mh7QfieAIjWagkJNt68Yfim6cjEzVSiLSeZ
# fdkr5dtFPTW6jATlWJdYeeDRGCyatf8R1hSjzSvdN8yWQPT9gzGCBkYwggZCAgEB
# MGowVjELMAkGA1UEBhMCUEwxITAfBgNVBAoTGEFzc2VjbyBEYXRhIFN5c3RlbXMg
# Uy5BLjEkMCIGA1UEAxMbQ2VydHVtIENvZGUgU2lnbmluZyAyMDIxIENBAhBDuzRB
# nypScc4/rofK9qMeMA0GCWCGSAFlAwQCAQUAoIGEMBgGCisGAQQBgjcCAQwxCjAI
# oAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYBBAGCNwIB
# CzEOMAwGCisGAQQBgjcCARUwLwYJKoZIhvcNAQkEMSIEICIQnhKuAZI4bb9Panyl
# G9zvk/Lzg0HFwkmOX3iUokKXMA0GCSqGSIb3DQEBAQUABIICAENnoIyRzcl6LZDF
# +h2awA5COM5zcJv0Gr1jCisyAna5vJUriDFXbLJ1c2hd+Ie/zHhNbUcpJuaGXhBC
# HTk91cHT5LPJW4fL8JA++OxtnvOJGiR+XSpfbn/8OfsKsokdVvrjsTmJDGK2zUMe
# /skvfjAXJyrOLC+HR3WNucKjvpPQ9KNNbEEU1WF3B776GAxjHyjLc8kCyo5Dn5fu
# gU3FEwfJ/UU0Hq5+bs4TtJmVNUI/aT7suIEfb0hShtksqa+nYL6EblQa55HpwGn8
# iVbaGpWz4+YboNepGiOq8H7bJqDQkbMzz5bNVOfjpq/xPrpSUHQoSi2sX55QrMbC
# TgN39PA5lhJMN1BaIIgCO4X8zP8Ru3v/W4IZ9wJrkSuTyBsYYW9hLHpPlDYTWDth
# Ji2QSaohOnUWl7RNZBnXCYhlmpXJv4c2dxCQYb+/VxXpTx9TSCOV3SazXuuNnS2Y
# zrOsoQ6W+FK9bkeGr4RQnYoG2evpWTDr2seor52wyUzNmEsZnXk0Y4GNTfAwhr33
# f0pe2QlGzNznjAgrBhl86AGZ5pqakMhYNfDf5cT9l1o2p0Tpt8ciIX8LU20dCSpQ
# Bw+O0mRUFRMa5EeMHphFnZ6n2PEd+Q1ibp8JzfJX0Vc762GFC+2NcvWDv9Zf6/GR
# eupWdXH4gM3IYtbIr2NRE05ZrTkLoYIDJjCCAyIGCSqGSIb3DQEJBjGCAxMwggMP
# AgEBMH0waTELMAkGA1UEBhMCVVMxFzAVBgNVBAoTDkRpZ2lDZXJ0LCBJbmMuMUEw
# PwYDVQQDEzhEaWdpQ2VydCBUcnVzdGVkIEc0IFRpbWVTdGFtcGluZyBSU0E0MDk2
# IFNIQTI1NiAyMDI1IENBMQIQCE/cM09+RU7bww+P+ZIYNTANBglghkgBZQMEAgEF
# AKBpMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTI2
# MDkxMzE5MzU1NFowLwYJKoZIhvcNAQkEMSIEIEwI9Du+vs9h6n6mPrgfGeeY9fWv
# 5eGn4E8VDNn9msQtMA0GCSqGSIb3DQEBAQUABIICAHq25eLt2SGG9oehoU3AGGQI
# 5uND4id3c9T6hwsabSyAm1HDPKkQdNYfXH/g58fViaxR7l+l2xop3Am458hi6zj6
# GAf1rlqUtnycTEWYRzBpv0w2WzGkOPm2xu7PnWFmOqK5c+6+Lhd7h/E70yHiXMEz
# 8j+axHPv76BK6BjSn2l9UqS2KZorS3c3hikEIR4AdOGANTos7U/CJhgxXhDsh8cF
# ZIVZ7LMZm9JgYdI0OdRLB/lcRofPNcYfvgU14Z7eQZonr1dqgAsGFpvDuJggLtVj
# 1WsDYxYHnQ/xf8ccvzyIZS2M74P+12WdvCupVGVW9q/Wg9qoWxql4RLqnpJd85qX
# ngNgKtF2pAHWLGHzh82WJmeFRymSNTp1NIonSWD8IEuAY2WjWd9QMqZtWjZA/Joj
# AFTFNasp9yyhIOg/IIWxnC/TQE0Eqo6uQoT9hISibOrVUr2e6872KyFAEe3+WVNy
# EJX+CUB8mSKpTKHynjto/DatvLKTB7xAWy1DNnn4wM+RRhUF/YDmUr7ji9/HHFlG
# Lb2A/D9P0gHN4FXxEAYqWlOmfpMEHqxk1azqNIVvF9AHGnIwUYegUs1UHTtauQd1
# H8wD55vNB14CFH2wwnd1HzaNQrOfG7x9RvRBp0ME6I4qCMuhrziGfmedmulfKN1y
# iAZ/NtMrrlVJTtDqDe9q
# SIG # End signature block