Search code examples
azure-devopsazure-pipelinesdatabase-projectdacpac

Can't Find Where the Invalid Database Platform Service is Being Configured

A database project was created using VS2022. When the code was checked in, it would not build, and the error was simply "...build--FAILED."

After the installation of VS2019 and a re-creation of the project, the Build Pipeline was happy. The build agent supports the following versions of SQL server via sqlpackage.exe (given to me from devops).

enter image description here

The .sqlproj is targeting AWS/RDS SQL Server version 2019 or SQL Version 2019.

enter image description here

The Build Pipeline produces a valid .dacpac file and the pipeline’s setup is below:

enter image description here

When I extract the model.xml from the .dacpac in the agent's artifacts drop, the configuration appears to show that the target is in fact 2019, or at least that seems to be what Sql150DatabaseSchemaProvider is targeting -->

<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.9" DspName="Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">

The error comes into play when a release is performed. The release is configured thusly:

enter image description here

Here is the error:

2023-11-07T20:18:16.2038422Z *** Could not deploy package. 2023-11-07T20:18:16.2040054Z Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

My understanding from this error message is that the .dacpac deploy is somehow configured or set to 2022?? or at least attempting to have that exe use Sql160DatabaseSchemaProvider for the deploy. I have been combing through all files in my project and can't find any place where that Schema Provider is being set.

Has anyone seen this error? It is like the build agent is producing a .dacpac for 2022 when devops is telling me it only supports only up to 2019.

I have set the project to all supported versions from what I have been told and can see that Schema Provider name change, with the targeted version embedded. I am hoping this is a simple oversight, however I have been turning knobs and flicking switches and now it has become an act of spinning wheels.

UPDATE:

After looking at the capabilities of the build machine, the setting below stuck out to me. It seems like DAC\130 could be setting the compatibility level of SqlPackage to SQL SERVER 2016, even though V2019 Enterprise is there. That would lead me to question if DacFx 130 has a problem targeting SQL Server 2019 instances. On the other hand, a local install on my laptop has 130, 140 and 150, so perhaps the first version is only listed under capabilities.

SqlPackage : C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe

VisualStudio_16.0 : C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\

Solution

  • a local install on my laptop has 130, 140 and 150 Do you have 160 on your local machine?

    From looking at your error message,"The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider is not valid. " It looks like it cannot find the correct SqlPackage.exe on your machine.

    I followed your steps to test at my self-hosted agent and found a key error message in the release log file.

    Dac Framework (installed with Visual Studio) not found on machine xxxxxxx
2023-11-08T08:35:31.4317748Z ##[debug]Executing command: C:\Program Files (x86)\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe

    It tried to find DAC from " C:\Program Files (x86)\Microsoft SQL Server\150\DAC" but when i checked the path the DAC folder don't exist.

    sql.proj

     <DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>

    When you create sql server database project in visual studio 2019, the default DatabaseSchemaProvider is 150.Please check the version of DatabaseSchemaProvider in your sql.proj and make sure you have installed the corresponding versions on your machine.

    If there is any misunderstanding, please feel free to let me know.

    Doc Referred:

    https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlDacpacDeploymentOnMachineGroupV0/README.md