Dotnet build issue with system database reference.

[DavidWiseman]

• SqlPackage or DacFx Version:0.1.3-preview
• .NET Framework (Windows-only) or .NET Core:.NET Core
• Environment (local platform and source/target platforms): Windows

Steps to Reproduce:

1. Create a new database project in Visual Studio
2. Follow instructions in this blog to get build working with dotnet build. https://erikej.github.io/ssdt/dotnet/2022/03/07/ssdt-dacpac-netcore.html
3. Build should work at this point. dotnet build /p:NetCoreBuild=true
4. Add a reference to the "master" system database in Visual Studio. This creates the following XML:

<ItemGroup>
    <ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac">
      <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac</HintPath>
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
    </ArtifactReference>
  </ItemGroup>

5. Build will now fail. dotnet build /p:NetCoreBuild=true

C:\Users\user1\.nuget\packages\microsoft.build.sql\0.1.3-preview\tools\netstandard2.1\Microsoft.Data.Tools.Schema.SqlTasks.targets(525,5): Build error SQL72027: File "C:\Users\user1\.nuget\packages\microsoft.build.sql\0.1.3-preview\tools\netstandard2.1\Extensions\Microsoft\SQLDB\Extensions\SqlServer\130\SqlSchemas\master.dacpac" does not exist. [c:\Git\TrimbleOSS\dba-dash\Database1\Database1.sqlproj]

Did this occur in prior versions? If not - which version(s) did it work in?

Not applicable to prior version

[kisantia]

@DavidWiseman Thanks for trying out the MIcrosoft.Build.Sql SDK and reporting this! I added this error to the ReadMe. $(DacPacRootPath) is a variable with the location of the system dacpacs that SSDT passes in when building a project, so when building from the command line, that will need to be passed in like /p:DacPacRootPath="C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE".

There's currently support for SDK-style sql projects in the Azure Data Studio insiders build with the sql database projects extension installed. Creating new SDK-style projects(empty and from a database), adding system database references, and building SDK-style projects are already supported in ADS if you're interested in trying it out.

[ErikEJ]

@kisantia Do you get the good table designer experience in ADS? Do you get model validation with Intellisense in ADS?

[kisantia]

Table designer is in preview in the latest ADS release and integration with sql projects is coming in the future. Model validation is not currently available in ADS.

[DavidWiseman]

Hi. Thank you for the quick reply to my issue - sorry it's taken a while to respond back. Passing the correct path from the commandline fixes the issue. 😊 I'm mostly interested in getting the project to build with dotnet build over msbuild so I can create a dacpac with a linux runner github action. I can then run SQL Server on docker and have some automated tests run. I guess my new problem might be where to point to for the system dacpacs on the github runner.

Thanks,

David

[dzsquared]

@kisantia Kim Santiago FTE Do you get the good table designer experience in ADS? Do you get model validation with Intellisense in ADS?

@ErikEJ to be clear, this wasn't to suggest that Visual won't get support for the SDK-style project file, but currently an optional workaround is to use Azure Data Studio insiders (or the command line option shared above).

[davidcorbin-atmosera]

Been spinning my wheels trying to create a dacpac i the context of a GitHub (Linux) runner ... I have Azure Data Studio with the latest Extension for using packages.... Is there a clean sample? [for example, anything that brings up ("C:\Program Files..." is clearly not intended for Ubuntu...

note: A Microsoft supported Extension on the GitHub Market Place for this would be IDEAL <>

[dzsquared]

@dcorbin-wintellect we're working on https://github.com/azure/sql-action, which has limited support for SDK-style project build (+deploy). At the moment the build support is in combination with deployment, but there are certainly scenarios where someone would want to hold off on deployment.

[DavidWiseman]

Thank you. It's not quite what I'm looking for but could be useful for some other stuff👍 What I'm interested in - I want to build the database project on github linux runner. It would need to handle the system dacpac reference. In terms of deployment - I'm interested in deploying to a SQL instance running on docker on the github runner for testing. My app will take care of the deployment - I just need to build the dacpac.

[dzsquared]

I see you opened an issue there - thank you! Will continue to discuss there 😄

edit: oh boy, I have not had enough coffee yet. The GH action I linked does support Linux, but needs a docs update to specify this.
I'll open issues to call out only building a dacpac + handling system dacpac references.

[zijchen]

We just released 3 system dacpac Nuget packages that can be referenced via PackageReference from projects built with Microsoft.Build.Sql 0.1.9-preview and up. Example:

<PackageReference Include="Microsoft.SqlServer.Dacpacs" Version="160.0.0" />

Available packages are:

• Microsoft.SqlServer.Dacpacs (versions 100.0.0 - 160.0.0)
• Microsoft.SqlServer.Dacpacs.Azure
• Microsoft.SqlServer.Dacpacs.Synapse

You can read more about it here: https://learn.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-database-project-extension-sdk-style-projects?view=sql-server-ver16#package-references

[uladz-zubrycki]

@zijchen I see that it's possible to specify a name of the dacpac to import via <DacpacName> child node. I'm wondering whether it's going to import both master and msdb, if there is no <DacpacName> specified.

Asking, since I'm not able to fix the warning SqlProcedure: has an unresolved reference to object [msdb].[dbo].[sp_send_dbmail].[@body]. in my builds.

[zijchen]

@uladz-zubrycki When no DacpacName is specified the master is used by default. There is no easy way to import both master and msdb currently. One way is to add PackageReference to master and then manually add an ArtifactReference to msdb:

<ItemGroup>
  <PackageReference Include="Microsoft.SqlServer.Dacpacs">
    <GeneratePathProperty>true</GeneratePathProperty>
    <DacpacName>master</DacpacName>
  </PackageReference>
  <ArtifactReference Include="$(PkgMicrosoft_SqlServer_Dacpacs)/tools/msdb.dacpac">
    <DatabaseVariableLiteralValue>msdb</DatabaseVariableLiteralValue>
  </ArtifactReference>
</ItemGroup>

[uladz-zubrycki]

@zijchen Thank you! Incredibly helpful. Will give it a try.