Now getting strange error in SQL Change Automation when used from VSTS / Azure DevOps

brookal

I have been using SQL Automation as part of our VSTS / Azure DevOps devlopment and release cycle so that when changes are made, the build is automatic.  However, a couple of days ago, the builds suddenly started failing on the build of our SQL Change Automation project. 

I get the following errors now:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\SqlChangeAutomation\SqlChangeAutomation.Data.Schema.Ssdt.targets(477,5): Error MSB4062: The "DBCleanTask" task could not be loaded from the assembly C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\SqlChangeAutomation\RedGate.Versioning.Client.MsBuild.dll. Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. Confirm that the <UsingTask> declaration is correct, that the assembly and all its dependencies are available, and that the task contains a public class that implements Microsoft.Build.Framework.ITask.

Process 'msbuild.exe' exited with code '1'.

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\SqlChangeAutomation\SqlChangeAutomation.Data.Schema.Ssdt.targets(110,5): Error MSB4062: The "DBBuildTask" task could not be loaded from the assembly C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\SqlChangeAutomation\RedGate.Versioning.Client.MsBuild.dll. Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. Confirm that the <UsingTask> declaration is correct, that the assembly and all its dependencies are available, and that the task contains a public class that implements Microsoft.Build.Framework.ITask.

Process 'msbuild.exe' exited with code '1'.

Which I don't understand....  I have attached the overall log file from the VSTS build if it helps.

Many thanks

Alan

Greg_Smulko

Hi Alan, sorry for the inconvenience caused. It's a regression introduced in the latest release 3.0.18326.
We're working on a fix and going to release a new version soon.

brookal

Hi Greg,

That explains it then

Many thanks for the update... Do you know how to get the sql change automation into the Azure DevOps pipeline... I have installed it via the marketplace but I don't seem to be able the add it into my pipeline as there is no Add button.

Greg_Smulko

I assume you clicked the Refresh button in the middle of the screen (between "Add tasks" and the search box)? 

For me it looks like this:


Maybe you need not only to install it, but also add it to your organization?

samdearden

Hi Greg,

Our builds are currently broken due to this 'regression issue'. 

Can you please indicate when a fix is expected?

Sam 

Greg_Smulko

Sam, we're going to release a fix tomorrow.
As a temporary workaround, you can pin to a specific version of SQL Change Automation in Azure DevOps:

brookal

Hi Greg,

What would be a good version number to use in this slot.

Alan

Greg_Smulko

Actually, I've taken a second look, and in your case, pinning the version is not possible, because you use "Visual Studio Build" rather than "Redgate SQL Change Automation Build". The versions to be put in this box are versions listed here: https://www.powershellgallery.com/packages/SqlChangeAutomation/3.0.1.2275, but in this case the "lastest" version would work fine.

It looks like you use local agents, so on these machines, you can just install the version 3.0.18324, however the recommended way would be to change your build definition to use the "Redgate SQL Change Automation Build" task.

brookal

Hi Greg, 

I have now spent the day changing my build pipeline to use the redgate sql chnge automation build task so we shall see what we have with that

Alan

InkZ

Our Team City build server is currently dead as we are suffering with this issue.  We are just going to live with it until the fix is released.

Some projects build via Visual Studio, so the plugin must have updated automatically.

brookal

Hi Greg,

I have now managed to sorting out the build pipeline to use the change automation task and it works again.... However, I couldn't get the Azure hosted2017 agent to use an Azure SQL database as the shadow database... something to do with permissions for cleaning etc...  However, as I have a build agent in a VM, I have continued to use that.  

Can you double check that the pipeline works correctly with an Azure SQL database as the shadow database.  I have created the empty database in the Azure portal, but the pipeline fails and then the database is deleted for some reason. 

Alan

Greg_Smulko

@InkZ, @brookal , we've just released a version that fixes the regression (3.0.18331). Once again sorry for the inconvenience caused.
If it's not a big problem, could you please check what version of .NET Framework is installed on machines where your builds were failing?
Regarding support for Shadow Database on Azure, we currently don't support such a scenario indeed, but actively working on improving it as we speak. For more info please see this help page.

InkZ

Hi Greg,

We are running .Net release 460805 on Server 2012 which is 4.7 I believe.

I've updated Change automation on the build server and everything is building again.

Many thanks for your help.

samdearden

Greg, I can confirm that the new version has fixed the issue. Thanks.