SQL - Deploy DACPAC from Package Parameter failing

[richardboyce]

Hi all,
After much effort, I have my ‘NuGet-wrapped’ DACPAC mostly deploying to the database server. However it fails near the start of the process. We’re using V2020.1.12

There are a few exceptions shown, but I can’t find how to turn these into things I can change:
| == Failed: Step 1: SQL - Report on DACPAC from Package Parameter ==
16:03:34 Fatal | The step failed: Activity SQL - Report on DACPAC from Package Parameter on a Worker failed with error ‘The remote script failed with exit code 1’.
16:03:34 Verbose | SQL - Report on DACPAC from Package Parameter completed

16:03:34 Verbose | Loading the target server dacpac for report and scripting. Filename: D:\DeploymentTools\Octopus\Work\20200922150303-9135-118\DACPACPackage\SQLDabcdef.ProjectA.20200922150304.dacpac
16:03:34 Verbose | Gathering generic database scoped configuration option
16:03:34 Verbose | Gathering users
16:03:34 Info | Gathering roles
16:03:34 Info | Gathering application roles
16:03:34 Info | Generating deploy report against server: SQLabcdef, database: projectA
16:03:34 Info | Gathering role memberships
16:03:34 Info | Gathering filegroups
16:03:34 Info | Gathering full-text catalogs
16:03:34 Info | Gathering assemblies
16:03:34 Info | Gathering certificates
16:03:34 Info | Gathering asymmetric keys
16:03:34 Verbose | Gathering symmetric keys
16:03:34 Verbose | Gathering encrypted symmetric keys
16:03:34 Verbose | An exception of type System.Management.Automation.MethodInvocationException was thrown.
16:03:34 Verbose | Gathering schemas

Can you help, or tell me where to look for more detail? The DBA says the last action on the database is “SELECT @@VERSION”. The following actions must be on the dacpac created from the target server?

Thanks

[donny.bell]

Hi @richardboyce,

Thank you for contacting Octopus Support.

Are you using integrated authentication for this step? If you wouldn’t mind uploading your process JSON via Octopus.com -> Sign-In -> Support, I can take a look for you.

I look forward to hearing back from you.

Regards,
Donny

[richardboyce]

Hi Donny, I have the json file ready to upload, but can’t see where in the support section.
Yes, we’re using integrated/windows auth

Richard

[donny.bell]

Hi @richardboyce,

I enabled it for your org. Refresh the page and give it another try.

Let me know if you still can’t see it.

Regards,
Donny

[richardboyce]

Sorry Donny, probably being dense, but I can’t see any way to upload on the support page (https://octopus.com/support)
Richard

[donny.bell]

Hi @richardboyce,

No problem. Here’s a screenshot:

Just click the org under your name in the top right, then click on the support tab.

Let me know if that works.

Regards,
Donny

[richardboyce]

Perfect, thanks Donny. Uploaded now
Richard

[donny.bell]

Hi @richardboyce,

Thank you for sending that over.

I’ll have a look and try to get this reproduced in a test environment. I’ll let you know what I find out.

If you have any questions or if something comes up, let me know.

Regards,
Donny

[richardboyce]

Good morning Donny. I have found the issue - I changed the target SQL type in the VS project to 2019 to try to fix an earlier issue. When I looked at the log this morning, I immediately saw:
“Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentCompatibilityException: A project which specifies SQL Server 2019 or Azure SQL Database Managed Instance as the target platform cannot be published to SQL Server 2017”

I don’t know how I missed that yesterday, but changing target database server to 2017 has fixed the problem.
Thanks for your support yesterday
Richard

[donny.bell]

Hi @richardboyce,

I’m glad to hear you were able to get it figured out.

If you need anything else, don’t hesitate to ask. We’re here to help!

Regards,
Donny