Sql150DatabaseSchemaProvider Support

dariokondratiuk's Avatar

dariokondratiuk

24 Mar, 2020 08:45 PM

I subscribed to this issue https://github.com/appveyor/ci/issues/3243 waiting for SQL Server 2019 to be ready to use.

Now that it's up there I've made the following changes:

Changed my sqlproj file form

Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider

To

Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider

Changed the services from mssql2016 to mssql2019.
And changed all the connection strings to (local)\SQL2019

Now, when I run this command

"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -source:dbDacFx="C:\projects\foo\DB\bin\CI\DB.dacpac" -dest:dbDacFx="%LocalDB%",dacpacAction=Deploy,CreateNewDatabase=True'

I'm getting

Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider 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.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider 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.
Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_METHOD. Error: Could not deploy package.
Error: Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider 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.
Error: Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider 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.

What am I missing?

  1. Support Staff 1 Posted by Feodor Fitsner on 24 Mar, 2020 08:50 PM

    Feodor Fitsner's Avatar

    Hi Dario,

    SQL Server 2019 is not yet fully integrated into build agent, so please add this command to your build to start the service:

    install:
    - ps: Start-Service 'MSSQL$SQL2019'
    
  2. 2 Posted by dariokondratiuk on 24 Mar, 2020 08:59 PM

    dariokondratiuk's Avatar

    Feodor, thanks, but now I'm getting a:

    Start-Service : Cannot find any service with service name 'MSSQL$SQL2019'.

  3. Support Staff 3 Posted by Feodor Fitsner on 24 Mar, 2020 09:00 PM

    Feodor Fitsner's Avatar

    SQL Server 2019 is available on Visual Studio 2019 image only.

  4. 4 Posted by dariokondratiuk on 24 Mar, 2020 09:23 PM

    dariokondratiuk's Avatar

    Feodor, how about this?

    Error Code: ERROR_DACFX_NEEDED_FOR_SQL_PROVIDER
    More Information: The SQL provider cannot run with dacpac option because of a missing dependency. Please make sure that DacFx is installed. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_DACFX_NEEDED_FO....

  5. Support Staff 5 Posted by Feodor Fitsner on 24 Mar, 2020 09:59 PM

    Feodor Fitsner's Avatar
  6. Support Staff 6 Posted by Feodor Fitsner on 24 Mar, 2020 11:16 PM

    Feodor Fitsner's Avatar

    OK, here is what I found.

    msdeploy.exe is looking for Microsoft.SqlServer.Dac.dll library in a number of "well-known" directories (most probably it takes them from the Registry) - see screenshot attached.

    None of those directories exists on Visual Studio 2019 image. And it's understandable as starting from late VS 2017 versions DacFx is a part of Visual Studio SSDT package and on VS 2019 image DacFx is located in C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150 directory.

    I've tried playing with copying that directory to some of those locations, but then msdeploy gives a different more cryptic errors, most probably because that 15.x DacFx version is far beyond of what's supported in msdeploy. Indeed, WebDeploy stopped evolving a long time ago.

    My suggestion for you is to switch deployment to sqlpackage.exe tool which is a part of VS SSDT and in PATH already. Look at this complete example for VS 2019 image: https://ci.appveyor.com/project/appveyor-tests/dacpac-sqlpackage-te...

  7. 7 Posted by dariokondratiuk on 25 Mar, 2020 12:54 PM

    dariokondratiuk's Avatar

    It worked!
    Thanks a lot man!

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac