We have been using octopus cloud and workers to deploy SQL DACPACs for some time however since 04/04/23 these are starting to fail - even attempting to deploy a previously successful build is failing.
This leads me to believe that there is something causing a problem on the server that the worker is running on however I am at a loss as to what is causing this
Any help would be appreciated
Extracting package 'D:\Octopus\Files\DB.CSP-Test@S799@6FD5B65FAD000148AD91BAB56365988F.nupkg' to 'D:\Octopus\Work\20230406095007-75092-1515\DACPACPackage'
April 6th 2023 10:50:11Info
Importing module SqlServer ...
April 6th 2023 10:50:11Info
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)
April 6th 2023 10:50:11Info
at System.Reflection.RuntimeAssembly.nLoadFile(String path, Evidence evidence)
April 6th 2023 10:50:11Info
at System.Reflection.Assembly.LoadFile(String path)
April 6th 2023 10:50:11Info
at CallSite.Target(Closure , CallSite , Type , Object )
April 6th 2023 10:50:11Info
--- End of inner exception stack trace ---
April 6th 2023 10:50:11Info
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
April 6th 2023 10:50:11Info
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
April 6th 2023 10:50:11Info
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
April 6th 2023 10:50:11Info
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
April 6th 2023 10:50:11Error
NotSpecified: Exception calling "LoadFile" with "1" argument(s): "The module was expected to contain an assembly manifest. (Exception from HRESULT: 0x80131018)"
April 6th 2023 10:50:11Error
At D:\Octopus\Work\20230406095007-75092-1515\Script.ps1:64 char:9
April 6th 2023 10:50:11Error
+ [Reflection.Assembly]::LoadFile($assemblyFile.FullName) | Out ...
April 6th 2023 10:50:11Error
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
April 6th 2023 10:50:11Error
at Load-SqlServerAssmblies, D:\Octopus\Work\20230406095007-75092-1515\Script.ps1: line 64
April 6th 2023 10:50:11Error
at Add-DACAssemblies, D:\Octopus\Work\20230406095007-75092-1515\Script.ps1: line 233
April 6th 2023 10:50:11Error
at Invoke-DacPacUtility, D:\Octopus\Work\20230406095007-75092-1515\Script.ps1: line 411
April 6th 2023 10:50:11Error
at <ScriptBlock>, D:\Octopus\Work\20230406095007-75092-1515\Script.ps1: line 649
April 6th 2023 10:50:11Error
at <ScriptBlock>, <No file>: line 1
April 6th 2023 10:50:11Error
at <ScriptBlock>, D:\Octopus\Work\20230406095007-75092-1515\Octopus.FunctionAppenderContext.ps1: line 211
April 6th 2023 10:50:11Error
at <ScriptBlock>, D:\Octopus\Work\20230406095007-75092-1515\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 4502
April 6th 2023 10:50:11Error
at <ScriptBlock>, <No file>: line 1
April 6th 2023 10:50:11Error
at <ScriptBlock>, <No file>: line 1
April 6th 2023 10:50:12Fatal
The remote script failed with exit code 1
April 6th 2023 10:50:12Fatal
The action Deploy CSP-Test on a Worker failed
I think I may have answered this in our email system as you sent a ticket into our support@octopus.com email address but for other customers benefit who are running into this issue please see the response I sent you via email:
Thank you for contacting Octopus Support and sorry you are running into an issue with the SQL - DACPAC step template, this is a known issue that was brought to our attention the other day by one of our users on our community Slack pages. If you have Slack installed, you can follow the conversation here.
In short:
On April 4th Microsoft upgraded their PowerShell SQLModule from version 21.1.18256 to 22.0.59, the new module will not load with PowerShell 5.1, which is what the SQL - DACPAC step template uses. The engineers took a look at this and mentioned they are possibly looking to depreciate the SQL - Deploy Dacapc step template so this does not happen to other customers, they did some digging and said this issue is the step template (the older SQL - Deploy Dacapc one) is trying to load a netstandard dll on windows PowerShell 5.1 which doesn’t work (it doesn’t load dlls equivalent to .net core, only net framework).
PowerShell 6.X and above supports .netcore and netstandard, we have a newer version of the SQL - DACPAC step template here (called SQL - Deploy DACPAC using SqlPackage ) that new template can load the newer SQLServer module, so would work as a permanent fix for this issue.
We have identified three fixes for this issue, two being workarounds if you are unable to switch over to the new step template:
Upgrade to the new step template I linked above, this is the permanent fix and will allow you to get module updates moving forward, this template has been confirmed to work for one of our customers but might not work for your use case so test it out on one project first.
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:
Hopefully, one of those fixes works for you, the two workarounds would force you to use an outdated SQLModule so you would not get any updates to that module when Microsoft pushes them out, we recommend trying the new step first and see if that works for you.
Hi Clare, as an update we have just used your first option and upgraded our 8 database projects to use the new DACPAC template and it successfully deployed all the projects through our dev and staging environments.
Kind Regards and thanks to @ste.wilson for raising the ticket in the first place.
Thanks for getting back to us and fantastic news you managed to get the new template to work, with 8 different database projects too! Also welcome to our forums as I can see this is the first time you have reached out on here.
Reach out in future if you need anything else as we are always on hand to help!