SQLCMD Variable Defaults not Present in Built DacPac

[chriseaton]

We are using sql command variables to define the server and database on a project reference, but the default values are not making their way into the dacpacs when we do a dotnet build. We tried using the dotnet build /p:SqlCmdVar__1=somevalue but it didn't net a difference. Using v.1.17.3.

Our sqlproj:

<Project Sdk="MSBuild.Sdk.SqlProj/1.17.3">
    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
        <RecoveryMode>Simple</RecoveryMode>
        <SqlServerVersion>Sql130</SqlServerVersion>
    </PropertyGroup>
    <PropertyGroup>
        <OutputPath>deploy\</OutputPath>
        <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
        <AppendRuntimeIdentifierToOutputPath>false</AppendRuntimeIdentifierToOutputPath>
    </PropertyGroup>
    <ItemGroup>
        <Content Remove="scripts\**\*.sql" />
    </ItemGroup>
    <ItemGroup>
        <ProjectReference Include="../Soft_Landing/Soft_Landing.sqlproj" DatabaseVariableLiteralValue="Soft_Landing" />
        <ProjectReference Include="../Soft_App/Soft_App.sqlproj" DatabaseVariableLiteralValue="Soft_App" />
        <ProjectReference Include="../ACK/ACK.sqlproj" DatabaseSqlCmdVariable="ACK" ServerSqlCmdVariable="EXTServer" />
    </ItemGroup>
    <ItemGroup>
        <SqlCmdVariable Include="ACK">
            <DefaultValue>ACK</DefaultValue>
            <Value>$(SqlCmdVar__1)</Value>
        </SqlCmdVariable>
        <SqlCmdVariable Include="EXTServer">
            <DefaultValue>192.168.1.1</DefaultValue>
            <Value>$(SqlCmdVar__2)</Value>
        </SqlCmdVariable>
    </ItemGroup>
</Project>

Then when examining the model.xml of the dacpac:

...

		<CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
			<Metadata Name="ACK" Value="" />
			<Metadata Name="EXTServer" Value="" />
		</CustomData>
	</Header>

We are using Azure Data Studio to perform the SQL Compare from the built dacpacs but they fail due the values on these vars missing.

I also tried adding the explicit values in the variables at the top of the sqlproj to no avail.

<PropertyGroup>
      <SqlCmdVar__1>ACK</SqlCmdVar__1>
</PropertyGroup>

[jmezach]

@chriseaton Yeah, I don't think we add those default values into the .dacpac currently. Probably isn't too hard to add, we would just need to have an example of how this works with the existing SSDT tooling. Do you happen to have an example to share?

[gicastel]

I somehow managed to write inside the dacpac (in the model.xml) the default value for the variables (see here). Unfortunately, what I needed was the default value to be added when deploying with sqlpackage, and that isn't happening.

[ErikEJ]

Seems like this somehow relates to #201 #204 and #241

[JohnYoungers]

@chriseaton / @Geims83 - was something needed on publish to actually use these values?

Using MSBuild.Sdk.SqlProj/2.6.0 I can see the default values are being populated in the dacpac, but when running sqlpackage /Action:Publish it appears the variable is empty when not specified on the command line

[gicastel]

@JohnYoungers IIRC, unfortunately the only way to make sqlpackage use the default values is to use a publish profile, but to do so you have to specify the /Profile parameters... so I don't know if it's worth.

[JohnYoungers]

Thank you @Geims83: is there another way to deploy that would utilize these values?

It doesn't seem like this change would have accomplished much otherwise