SQLCMD variables not reflecting in SQL from publish profile xml

[ashishmg-gds]

Hi , I have a dev.profile.xml file that i use to set environment level values for building the dacpac etc. I defined a sqlcmd variable in this publish profile as below :

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <!-- <IncludeTransactionalScripts>True</IncludeTransactionalScripts> -->
    <DeployScriptFileName>deploy.sql</DeployScriptFileName>
    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludeDatabaseRoles>True</ExcludeDatabaseRoles>
    <CreateNewDatabase>False</CreateNewDatabase>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <DoNotDropLogins>True</DoNotDropLogins>
    <DoNotDropUsers>True</DoNotDropUsers>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropApplicationRoles>True</DoNotDropApplicationRoles>
    <DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <TargetConnectionString>Data Source=xxxx;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Integrated Security=False;Trusted_Connection=False</TargetConnectionString>
    <TargetDatabaseName>xxxx</TargetDatabaseName>
    <TargetResourceGroup>xxxx</TargetResourceGroup>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
  
  <ItemGroup>
    <SqlCmdVariable Include="ADLSSeedLocation">
      <DefaultValue>abfss://[email protected]</DefaultValue>
      <Value>abfss://[email protected]</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

And Im using this variable to create a External Data Source as below :

CREATE EXTERNAL DATA SOURCE [AzureDataLakeStorage]
    WITH (
    TYPE = HADOOP,
    LOCATION = N'$(ADLSSeedLocation)',
    CREDENTIAL = [dummycreds]
    );

But each time I run build, driftreports etc , Im getting below warning and error :

*** The following SqlCmd variables are not defined in the target scripts: ADLSSeedLocation.

[jmezach]

@ashishmg-gds We do not currently support publish profiles with MSBuild.Sdk.SqlProj. Our general recommendation is to only use MSBuild.Sdk.SqlProj to produce the .dacpac file and then use SqlPackage.exe to deploy said .dacpac to a running SQL Server somewhere. SqlPackage.exe does support publish profiles using the /Profile: parameter.

That being said the publish profile you shared looks like a regular MSBuild file so I guess you could <Import> it in your project file and it would probably work since we recognise most (if not all) of those properties when running dotnet publish as well. But as I said, this isn't our recommendation.

[ashishmg-gds]

@jmezach Im using sqlpackage with /Profile option to use Publish Profile. Yet this error pops up. Is there anything wrong Im doing here to reference the SQLCMD variable ? As a workaround I setup an environment variable instead of SQLCMD variable and used the same $() notation to reference and works like a charm

[jmezach]

@ashishmg-gds Hmm, that's interesting. In theory that should work. Perhaps this is related to #248?

[jeffrosenberg]

I believe the message you posted is just a warning, not an error, correct? It implies that you've passed in a SqlCmd variable that isn't actually needed. When you run the publish, does it succeed or fail, and are there any other errors? And can your confirm that your [AzureDataLakeStorage] external data source actually appears in the generated script?

[ErikEJ]

Closing as the requested additional information was never supplied.