Dacpac database classic release deployment group not working.. but it says it is

Question

Been banging up against walls for the last few days on this: I have a database project with pre and post deployment files. I build and copy those to my bin no issue. I have a pipeline that creates a .dacpac file for me.

I can verify the dacpac does the changes when I run this script locally via powershell:

# Define variables
$serverName = "'(LocalDb)\MSSQLLocalDB'"
$databaseName = "_DB"
#$dacpacPath = "'$(System.DefaultWorkingDirectory)/ohsrc_db/ohsrc_db\_DB.dacpac'"
$dacpacPath = "'C:\Projects\PHSA\OHSSolutions\API\OHSRC_DB\bin\Debug\_DB.dacpac'"

# Build the SQLPackage command
$sqlPackageCmd = "sqlPackage.exe"
$publishCmdArgs = "/Action:Publish /SourceFile:$dacpacPath /TargetDatabaseName:$databaseName /TargetServerName:$serverName /p:DropObjectsNotInSource='True'"

# Execute the SQLPackage command
Write-Output $publishCmdArgs
dotnet tool install -g microsoft.sqlpackage
Invoke-Expression  "$sqlPackageCmd $publishCmdArgs"

However after I am working with azure dev ops releases product where I am doing a classic release pipeline. I have tried taking the same as above (after changing source path) but keep getting the following error:

2024-01-25T20:40:29.4261080Z ========================== Starting Command Output ===========================
2024-01-25T20:40:29.4444154Z ##[command]"C:\WINDOWS\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy Unrestricted -Command ". 'C:\azagent\A2\_work\_temp\a4cb565f-56b8-4cc0-b822-166849a904ed.ps1'"
2024-01-25T20:40:29.8057735Z /Action:Publish /SourceFile:'C:\azagent\A2\_work\r1\a/_db/_db/_DB.dacpac' /TargetDatabaseName:_DB /TargetServerName:'(LocalDb)\MSSQLLocalDB' /p:DropObjectsNotInSource='True'
2024-01-25T20:40:30.0157286Z Tool 'microsoft.sqlpackage' is already installed.
2024-01-25T20:40:32.0795454Z Invoke-Expression : The term 'sqlPackage.exe' is not recognized as the name of a cmdlet, function, script file, or 
2024-01-25T20:40:32.0795752Z operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try 
2024-01-25T20:40:32.0795946Z again.
2024-01-25T20:40:32.0796260Z At C:\azagent\A2\_work\_temp\a4cb565f-56b8-4cc0-b822-166849a904ed.ps1:17 char:1
2024-01-25T20:40:32.0796542Z + Invoke-Expression  "$sqlPackageCmd $publishCmdArgs"
2024-01-25T20:40:32.0796694Z + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2024-01-25T20:40:32.0797163Z     + CategoryInfo          : ObjectNotFound: (sqlPackage.exe:String) [Invoke-Expression], CommandNotFoundException

setting the $sqlPackageCmd = "C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlPackage.exe"

results in more errors..

giving up sort of on this I try the traditional (since it is very traditional), of using the Sql Server Dacpac deploy task . Tried both server and connection string. Same result where it says it works.. but actually doesn't work at all.

steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Deploy _DB'
  inputs:
    DacpacFile: '$(System.DefaultWorkingDirectory)/_db/_db/_DB.dacpac'
    TargetMethod: connectionString
    ConnectionString: 'Data Source=(localDb)\MSSQLLocalDB;Initial Catalog=_DB;Integrated Security=True;'
    AdditionalArguments: '/DiagnosticsFile:$(System.DefaultWorkingDirectory)/output.log'

and in this case it reports everything to be working.. however nothing actually worked... I am verifying this by doing new builds with pre and post deployment.. dropping tables , creating tables etc. Very frustrating.

2024-01-25T19:46:13.3819927Z Task         : SQL Server database deploy
2024-01-25T19:46:13.3819964Z Description  : Deploy a SQL Server database using DACPAC or SQL scripts
2024-01-25T19:46:13.3820016Z Version      : 0.198.0
2024-01-25T19:46:13.3820044Z Author       : Microsoft Corporation
2024-01-25T19:46:13.3820077Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
2024-01-25T19:46:13.3820136Z ==============================================================================
2024-01-25T19:46:30.7909620Z Publishing to database '_DB' on server '(localDb)\MSSQLLocalDB'. 
2024-01-25T19:46:30.7910039Z Initializing deployment (Start) 
2024-01-25T19:46:30.7910244Z Initializing deployment (Complete) 
2024-01-25T19:46:30.7910463Z Analyzing deployment plan (Start) 
2024-01-25T19:46:30.7910674Z Analyzing deployment plan (Complete) 
2024-01-25T19:46:30.7911350Z Updating database (Start) 
2024-01-25T19:46:30.7911517Z Update complete. 
2024-01-25T19:46:30.7911698Z Updating database (Complete) 
2024-01-25T19:46:30.7911909Z Successfully published database. 
2024-01-25T19:46:30.7912608Z Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection 
2024-01-25T19:46:30.7912941Z Time elapsed 0:00:14.60 
2024-01-25T19:46:30.7913050Z 

Does anyone have a working powershell using a sqlpackage kind of like the way I am trying to? I don't want to migrate this to next environments until I can get it working locally.

------------UPDATE------------

I was super curious about the dacpac project that kept saying everything worked correctly so I went to the logs. within the logs is a path to the actual sql logs located at :

C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB.error.log

Within that log file for the relevant deploy I see the following:

.... 
2024-01-25 14:06:49.19 Server      The service account is 'WORKGROUP\MACHINE NAME$'. This is an informational message; no user action is required.
2024-01-25 14:06:49.19 Server      Command Line Startup Parameters:
     -c
     -S "MSSQL15E.LOCALDB" <-- is it possible I am somehow connecting to a different instance of sql server? I am running version 19 / 2019
     -s "LOCALDB#DEDEC1FF"
     -d "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\master.mdf"
     -l "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\mastlog.ldf"
     -e "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\error.log"
...

2024-01-25 14:06:49.54 spid18s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2024-01-25 14:06:49.55 spid14s     Starting up database 'mssqlsystemresource'.
2024-01-25 14:06:49.55 spid14s     The resource database build version is 15.00.4153. This is an informational message only. No user action is required.
2024-01-25 14:06:49.56 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2024-01-25 14:06:49.56 spid21s     Starting up database 'msdb'.
2024-01-25 14:06:49.56 spid23s     Starting up database '_DB'. <--- This is my database that I cannot see 
2024-01-25 14:06:49.56 spid24s     Starting up database '_TESTDB'.
2024-01-25 14:06:49.58 spid14s     Starting up database 'model'.
2024-01-25 14:06:49.59 spid24s     Parallel redo is started for database '_TESTDB' with worker pool size [4]. <--- this was another test
2024-01-25 14:06:49.60 spid23s     Parallel redo is started for database '_DB' with worker pool size [4].
2024-01-25 14:06:49.60 spid24s     Parallel redo is shutdown for database '_TESTDB' with worker pool size [4].


BELOW YOU CAN SEE THE TRANSACTIONS APPLIED...however I can't see this db 
2024-01-25 14:06:49.60 spid23s     31 transactions rolled forward in database '_DB' (7:0). This is an informational message only. No user action is required.
2024-01-25 14:06:49.60 spid14s     Clearing tempdb database.

NO ROLLBACKS 
2024-01-25 14:06:49.61 spid23s     0 transactions rolled back in database '_DB' (7:0). This is an informational message only. No user action is required.
2024-01-25 14:06:49.61 spid23s     Parallel redo is shutdown for database 'OHSRC_DB' with worker pool size [4].
2024-01-25 14:06:49.65 spid14s     Starting up database 'tempdb'.
2024-01-25 14:06:49.78 spid21s     The Service Broker endpoint is in disabled or stopped state.
2024-01-25 14:06:49.78 spid21s     The Database Mirroring endpoint is in disabled or stopped state.
2024-01-25 14:06:49.80 spid21s     Service Broker manager has started.
2024-01-25 14:06:49.80 spid9s      Recovery is complete. This is an informational message only. No user action is required.
2024-01-25 14:06:50.15 spid51      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2024-01-25 14:06:50.16 spid51      Using 'xpstar.dll' version '2019.150.4153' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

below you can see a screenshot of the dbs that seem to be present in the ssms instance... so weird.. note: you do not see the foo database in the list above but it is below.

Answer 1

Your powershell task reports:

Invoke-Expression : The term 'sqlPackage.exe' is not recognized as
the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.

Check this link: SqlPackage in development pipelines.

Try to use an explicit path: C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlPackage.exe

or switch work-directory to: C:\Program Files\Microsoft SQL Server\160\DAC\bin\