Issue with Octopus after upgrading SQL Server 2017 to 2019

[sre.admin]

After we upgraded our SQL Server from 2017 to 2019. We could deploy DACPAC to our SQL Server instance we have this error

Unable to find DACPAC assemblies in C:\Program Files\Microsoft SQL Server\150 C:\Program Files (x86)\Microsoft SQL Server\150 or child folders, attempting to use SqlServer PowerShell module …

June 8th 2023 18:51:20

Info

PowerShell module SqlServer not present, downloading temporary copy …

June 8th 2023 18:51:58

Info

Importing module SqlServer …

June 8th 2023 18:51:58

Info

System.Management.Automation.MethodInvocationException: Exception calling “LoadFile” with “1” argument(s): “The module was expected to contain an assembly manifest. (Exception from HRESULT: 0x80131018)” —> System.BadImageFormatException: The module was expected to contain an assembly manifest. (Exception from HRESULT: 0x80131018)

June 8th 2023 18:51:58

Info

at System.Reflection.RuntimeAssembly.nLoadFile(String path, Evidence evidence)

June 8th 2023 18:51:58

Info

at System.Reflection.Assembly.LoadFile(String path)

June 8th 2023 18:51:58

Info

at CallSite.Target(Closure , CallSite , Type , Object )

June 8th 2023 18:51:58

Info

— End of inner exception stack trace —

June 8th 2023 18:51:58

Info

at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)

June 8th 2023 18:51:58

Info

at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)

June 8th 2023 18:51:58

Info

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

June 8th 2023 18:51:58

Info

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

June 8th 2023 18:51:58

Error

NotSpecified: Exception calling “LoadFile” with “1” argument(s): “The module was expected to contain an assembly manifest. (Exception from HRESULT: 0x80131018)”

June 8th 2023 18:51:58

Error

At C:\Octopus\Work\20230608225213-165341-1011\Script.ps1:64 char:9

June 8th 2023 18:51:58

Error

• [Reflection.Assembly]::LoadFile($assemblyFile.FullName) | Out …

June 8th 2023 18:51:58

Error

June 8th 2023 18:51:58

Error

at Load-SqlServerAssmblies, C:\Octopus\Work\20230608225213-165341-1011\Script.ps1: line 64

June 8th 2023 18:51:58

Error

at Add-DACAssemblies, C:\Octopus\Work\20230608225213-165341-1011\Script.ps1: line 229

June 8th 2023 18:51:58

Error

at Invoke-DacPacUtility, C:\Octopus\Work\20230608225213-165341-1011\Script.ps1: line 405

June 8th 2023 18:51:58

Error

at , C:\Octopus\Work\20230608225213-165341-1011\Script.ps1: line 663

June 8th 2023 18:51:58

Error

at , : line 1

June 8th 2023 18:51:58

Error

at , C:\Octopus\Work\20230608225213-165341-1011\Octopus.FunctionAppenderContext.ps1: line 211

June 8th 2023 18:51:58

Error

at , C:\Octopus\Work\20230608225213-165341-1011\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 5478

June 8th 2023 18:51:59

Error

at , : line 1

June 8th 2023 18:51:59

Error

at , : line 1

June 8th 2023 18:51:59

Fatal

The remote script failed with exit code 1

[paraic.oceallaigh]

Hi @sre.admin
I have answered your issue in our helpdesk but for anyone else having the issue I will post the response here:

This is a known issue we have come across a few months back when Microsoft upgraded their Powershell SQL Module. This broke the DACPAC step template, as it would not load the correct assemblies.

Long story short, we have an upgraded template which will hopefully resolve this issue for you here:

Octopus Deploy Library

If for some reason you are unable to upgrade the template there are a couple of workarounds to get you going again:

• Workaround 1 is to use the old SQL - DACPAC step template but change the code in the template to force the old SQLModule:
  • To do this you would need to update line 49 of the step template to this…
    Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -RequiredVersion 21.1.18256 -Force
• Workaround 2 is to add that specific module and force the old version to the target by running the below code in PowerShell, this would need to be run (installed) on the target the step is configured to use:
  • install-module sqlserver -AllowClobber -scope AllUsers -RequiredVersion 21.1.18256

Let us know how you get on and if you need more help give us a shout.

Kind regards,
Paraic