Publish-DbaDacPackage appears to ignore DacFxPath option

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Capture

The Element or Annotation class SqlIndex does not contain the Property class Online.

This error comes from the fact that the dacpac I am trying to publish contains an "online" index. Recent changes to DacFx require the latest version which dbatools.library does not contain.

I therefore tried to use the -DacFxPath option (passing in the location of the latest Microsoft.SqlServer.Dac.dll) of Publish-DbaDacPackage but still received the same error.

I then passed in a completely invalid path to -DacFxPath - and received no warning or error that the dll could not be found but still received the "SqlIndex" error from above.

Finally I copy/pasted all the files from the latest SqlPackage download into the dbatools.library core\lib and desktop\lib folders. The publish of the database then worked without error.

This leads me to believe that Publish-DbaDacPackage always looks in the same place for DacFx and ignores the DacFxPath option.

Steps to Reproduce

Import-Module dbatools -PassThru

$TargetServer = "localhost" $TargetDatabaseName = "test-ssdt" $DacPacPath = "C:\git\test-ssdt\test-ssdt-db\bin\Debug\test-ssdt-db.dacpac" $OutputScriptPath = "C:\git\test-ssdt\"

$SqlPackageDllLocation = "C:\sqlpackage-win-x64-en-162.3.563.1\Microsoft.SqlServer.Dac.dll"

$DeployParams = @{ SqlInstance = $TargetServer Database = $TargetDatabaseName Path = $DacPacPath OutputPath = $OutputScriptPath DacFxPath = $SqlPackageDllLocation }

Publish-DbaDacPackage @DeployParams -EnableException

**Note I cannot attach the dacpac for your debugging purposes due to it not being a supported file type

Please confirm that you are running the most recent version of dbatools

2.1.17

Other details or mentions

No response

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

PSVersion 7.4.2 PSEdition Core GitCommitId 7.4.2 OS Microsoft Windows 10.0.19045 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU13) (KB5036432) - 16.0.4125.3 (X64) May 1 2024 15:05:56 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19045: ) (Hypervisor)

.NET Framework Version

.NET 8.0.4

The parameter was added about 6 years ago but is currently not referenced anywhere in the code.

When it was added, it was referenced in the begin block:

        if (Test-Bound -Not -ParameterName 'DacfxPath'){
            $dacfxPath = "$script:PSModuleRoot\bin\smo\Microsoft.SqlServer.Dac.dll"
        }
        if ((Test-Path $dacfxPath) -eq $false) {
            Stop-Function -Message 'No usable version of Dac Fx found.' -EnableException $EnableException
        }
        else {
            try {
                Add-Type -Path $dacfxPath
                Write-Message -Level Verbose -Message "Dac Fx loaded."
            }
            catch {
                Stop-Function -Message 'No usable version of Dac Fx found.' -EnableException $EnableException -ErrorRecord $_
            }
        }

The code was removed with the initial 2.0 release by @potatoqualitee : https://github.com/dataplat/dbatools/commit/0eb72452a71318acdec87fbb68bcd8a8a3f6202a#diff-803eb26d8e67d4f3a2814409d1c0b812a984d038811e56d4345240e0e4ecd24a

The removed code:

        if ((Test-Bound -Not -ParameterName 'DacfxPath') -and (-not $script:core)) {
            $dacfxPath = "$script:PSModuleRoot\bin\smo\Microsoft.SqlServer.Dac.dll"

            if ((Test-Path $dacfxPath) -eq $false) {
                Stop-Function -Message 'No usable version of Dac Fx found.' -EnableException $EnableException
                return
            } else {
                try {
                    Add-Type -Path $dacfxPath
                    Write-Message -Level Verbose -Message "Dac Fx loaded."
                } catch {
                    Stop-Function -Message 'No usable version of Dac Fx found.' -EnableException $EnableException -ErrorRecord $_
                }
            }
        }

I would suggest to add this code back in at the end of the begin block:

        if ($DacFxPath) {
            try {
                Add-Type -Path $DacFxPath
                Write-Message -Level Verbose -Message "Dac Fx loaded from [$DacFxPath]."
            } catch {
                Stop-Function -Message 'Dac Fx could not be loaded from [$DacFxPath].' -ErrorRecord $_
                return
            }
        }

dataplat / dbatools