master dacpac reference fails while deploys

Question

master dacpac reference fails while deploys

bheemvennapureddy opened this issue 6 months ago · 18 comments

bheemvennapureddy commented 6 months ago

master dacpac reference works during builds but fails during deploys - similar issue related to #51 but the DatabaseVariableLiteralValue doesn't help with sqlpackage deploy for some reason.

The major difference I see is the old way the master.dacpac is attached as separate dacpac in the zip for deploy where as with new SDK style it is embedded into one.

Adding both dacpacs for reference

CustomerScoringDatabase.zip

CustomerScoringDatabase.zip is the zip we use for deploy from old way

CustomerScoringDatabaseNewSDK.zip

New SDK is created with MSBuild.Sdk.SqlProj

Answer 1 · 2024-07-09T11:40:09.000Z

Target Connection String: Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Connection Timeout=120;MultipleActiveResultSets=False;Max Pool Size=200;Initial Catalog=UnitTests_630187_687c32b6-cfc9-5f5d-4d8d-5b2679c82883_CustomerScoringDatabase

deploy string for unit tests in the CI

What went wrong:
*** An error occurred during deployment plan generation. Deployment cannot continue.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
Warning SQL72025: No file was supplied for reference master.dacpac; deployment might fail. When package was created, the original referenced file was located master.dacpac.

An error occurred while adding references.  Deployment cannot continue.

Answer 2 · 2024-07-09T11:42:02.000Z

New Sqlproj file content below.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="MSBuild.Sdk.SqlProj" Version="2.7.2" />
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <SqlServerVersion>Sql150</SqlServerVersion>
    <SuppressTSqlWarnings>71558,71502</SuppressTSqlWarnings>
    <TargetName>CustomerScoringDatabase</TargetName>
    <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>None</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'SqlConvert|AnyCPU' ">
    <OutputPath>bin\SqlConvert\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>None</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <ItemGroup>
    <Build Include="dbo\Tables\AgencyDetailXmlTemplateTransform.sql" />
    <Build Include="dbo\Tables\Global.sql" />
    <Build Include="dbo\Tables\AgencyDetailAttribute.sql" />
    <Build Include="dbo\Tables\AgencyReport.sql" />
    <Build Include="dbo\Tables\AgencyCertificate.sql" />
    <Build Include="dbo\Tables\ScoreLoanMatrixDeleted.sql" />
    <Build Include="dbo\Tables\AgencyMaster.sql" />
    <Build Include="dbo\Tables\LocationLoanProduct.sql" />
    <Build Include="dbo\Tables\DenialReason.sql" />
    <Build Include="dbo\Tables\ScoringItemMaster.sql" />
    <Build Include="dbo\Tables\UserPermission.sql" />
    <Build Include="dbo\Tables\VendorLocationCredential.sql" />
    <Build Include="dbo\Tables\RuleConfig.sql" />
    <Build Include="dbo\Tables\RuleExpression.sql" />
    <Build Include="dbo\Tables\ScoreDetail.sql" />
    <Build Include="dbo\Tables\RuleResult.sql" />
    <Build Include="dbo\Tables\RuleHeader.sql" />
    <Build Include="dbo\Tables\VendorMatrixToSpeedyMatrix.sql" />
    <Build Include="dbo\Tables\ScoreSummary.sql" />
    <Build Include="dbo\Tables\ModelType.sql" />
    <Build Include="dbo\Tables\ScoreLoanMatrix.sql" />
    <Build Include="dbo\Tables\RuleHeaderEdit.sql" />
    <Build Include="dbo\Tables\ScoreProductSummary.sql" />
    <Build Include="dbo\Tables\Metro2Format.sql" />
    <Build Include="dbo\Tables\RuleExpressionEdit.sql" />
    <Build Include="dbo\Tables\PointValue.sql" />
    <Build Include="dbo\Tables\RuleResultEdit.sql" />
    <Build Include="dbo\Tables\Country.sql" />
    <Build Include="dbo\Tables\LoanApplication.sql" />
    <Build Include="dbo\Tables\AgencyDetail.sql" />
    <Build Include="dbo\Tables\RuleHeaderType.sql" />
    <Build Include="dbo\Tables\DenialReasonType.sql" />
    <Build Include="dbo\Tables\RuleHeaderDetail.sql" />
    <Build Include="dbo\Tables\ConversionMatrixIndicator.sql" />
    <Build Include="dbo\Tables\LoanApplicationExpense.sql" />
    <Build Include="dbo\Tables\ScoringHealthCheckLog.sql" />
    <Build Include="dbo\Tables\Claim.sql" />
    <Build Include="dbo\Tables\ScoringHealthCheck.sql" />
    <Build Include="dbo\Tables\SecurityGroup.sql" />
    <Build Include="dbo\Tables\SecurityGroupClaim.sql" />
    <Build Include="dbo\Tables\UserSecurityGroup.sql" />
    <Build Include="dbo\Tables\UserClaim.sql" />
    <Build Include="dbo\Tables\MultiScoreMatrix.sql" />
    <Build Include="dbo\Tables\LoanApplicationProduct.sql" />
    <Build Include="dbo\Tables\LoanApplicationAddress.sql" />
    <Build Include="dbo\Tables\LoanApplicationIncome.sql" />
    <Build Include="dbo\Tables\ModelGroup.sql" />
    <Build Include="dbo\Tables\ScoringTestName.sql" />
    <Build Include="dbo\Tables\LoanApplicationEmployer.sql" />
    <Build Include="dbo\Tables\ScoreDenialReason.sql" />
    <Build Include="dbo\Tables\LoanApplicationAffordSurvey.sql" />
    <Build Include="dbo\Tables\ModelGroupDetail.sql" />
    <Build Include="dbo\Tables\AgencyDataDeviceId.sql" />
    <Build Include="dbo\Tables\ModelGroupPrimaryModel.sql" />
    <Build Include="dbo\Tables\Config.sql" />
    <Build Include="dbo\Tables\ModelGroupApplyToAllModel.sql" />
    <Build Include="dbo\Tables\RuleGroup.sql" />
    <Build Include="dbo\Tables\ScoreSummaryAgencyDataXref.sql" />
    <Build Include="dbo\Tables\AgencyData.sql" />
    <Build Include="dbo\Tables\Location.sql" />
    <Build Include="dbo\Views\vw_ModelGroupDetailConfig.sql" />
    <Build Include="dbo\Functions\FN_Spell_LinkOperator.sql" />
    <Build Include="dbo\Functions\FN_Spell_TimeCode.sql" />
    <Build Include="dbo\Functions\DatePrep.sql" />
    <Build Include="dbo\Functions\FN_DateNoTime.sql" />
    <Build Include="dbo\Functions\FN_Spell_UserPermission.sql" />
    <Build Include="dbo\Functions\FN_Date_YYYYMMDD.sql" />
    <Build Include="dbo\Functions\FN_Spell_ModelType.sql" />
    <Build Include="dbo\Functions\FN_Spell_RuleHeaderType.sql" />
    <Build Include="dbo\Functions\FN_Spell_EvalOperator.sql" />
    <Build Include="dbo\Stored Procedures\usp_ScoreDetail_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleExpression_Delete.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyData_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleResult_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleResult_Delete.sql" />
    <Build Include="dbo\Stored Procedures\usp_Util_ActivateScoringTestRules.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleResult_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleResult_Update.sql" />
    <Build Include="dbo\Stored Procedures\usp_Rule_Delete.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleConfig_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_GetRulesToExecute.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleHeader_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReportMap_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReport_GetByScoringItemMaster.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReport_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReport_Update.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReport_Delete.sql" />
    <Build Include="dbo\Stored Procedures\usp_MappingConflicts_Check.sql" />
    <Build Include="dbo\Stored Procedures\usp_MappingConflicts_Get.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleHeader_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleHeader_Update.sql" />
    <Build Include="dbo\Stored Procedures\usp_ScoreSummary_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_ScoreDetail_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyMaster_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyReport_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyMaster_Update.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyMaster_Delete.sql" />
    <Build Include="dbo\Stored Procedures\usp_AgencyMaster_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleExpression_Insert.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleExpression_Update.sql" />
    <Build Include="dbo\Stored Procedures\usp_RuleExpression_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_ScoringItemMaster_Read.sql" />
    <Build Include="dbo\Stored Procedures\usp_GenerateScoreReport.sql" />
    <Build Include="dbo\Stored Procedures\usp_Scoring_GetAgencyData.sql" />
    <Build Include="dbo\Tables\ScorePendingReason.sql" />
    <Build Include="dbo\Tables\PendingReason.sql" />
    <Build Include="dbo\Tables\LoanApplicationIdentification.sql" />
    <Build Include="dbo\Tables\VendorMatrixToSpeedyMatrixHistory.sql" />
    <Build Include="dbo\Tables\ScoringItemMasterHistory.sql" />
    <Build Include="dbo\Tables\ScoreLoanMatrixHistory.sql" />
    <Build Include="dbo\Tables\RuleHeaderHistory.sql" />
    <Build Include="dbo\Tables\RuleExpressionHistory.sql" />
    <Build Include="dbo\Tables\RuleConfigHistory.sql" />
    <Build Include="dbo\Tables\ModelGroupPrimaryModelHistory.sql" />
    <Build Include="dbo\Tables\ModelGroupHistory.sql" />
    <Build Include="dbo\Tables\ModelGroupDetailHistory.sql" />
    <Build Include="dbo\Tables\ModelGroupApplyToAllModelTypeExclusionHistory.sql" />
    <Build Include="dbo\Tables\ModelGroupApplyToAllModelTypeExclusion.sql" />
    <Build Include="dbo\Tables\ModelGroupApplyToAllModelHistory.sql" />
    <Build Include="dbo\Tables\ConfigHistory.sql" />
    <Build Include="dbo\Tables\AgencyReportHistory.sql" />
    <Build Include="dbo\Tables\AgencyMasterHistory.sql" />
    <Build Include="dbo\Tables\AgencyDetailHistory.sql" />
    <Build Include="dbo\Tables\ScoreProductSummaryConversionMatrixIndicator.sql" />
    <Build Include="dbo\Tables\ConversionMatrixIndicatorHistory.sql" />
    <Build Include="dbo\Tables\AgencyProduct.sql" />
    <Build Include="dbo\Tables\AgencyDetailProductXRef.sql" />
    <Build Include="dbo\Tables\LocationHistory.sql" />
    <Build Include="dbo\Tables\LocationLoanProductHistory.sql" />
    <Build Include="dbo\Tables\PointValueHistory.sql" />
    <Build Include="dbo\Tables\RuleGroupHistory.sql" />
    <Build Include="dbo\Tables\RuleHeaderDetailHistory.sql" />
    <Build Include="dbo\Tables\ScoringHealthCheckHistory.sql" />
    <Build Include="dbo\Tables\SecurityGroupHistory.sql" />
    <Build Include="dbo\Tables\SecurityGroupClaimHistory.sql" />
    <Build Include="dbo\Tables\UserSecurityGroupHistory.sql" />
    <Build Include="dbo\Tables\VendorLocationCredentialHistory.sql" />
    <Build Include="dbo\Tables\AgencyDetailAttributeHistory.sql" />
    <Build Include="dbo\Tables\DenialReasonHistory.sql" />
    <Build Include="dbo\Tables\RuleResultHistory.sql" />
    <Build Include="dbo\Tables\VendorReportReasonLog.sql" />
    <Build Include="dbo\Tables\VendorReportReason.sql" />
    <Build Include="dbo\Tables\ScoreRequest.sql" />
    <Build Include="dbo\Stored Procedures\usp_ScoreSummary_Update.sql" />
    <Build Include="dbo\Tables\MarketProcess.sql" />
    <Build Include="dbo\Tables\MarketProcessHistory.sql" />
    <Build Include="dbo\Tables\MarketProcessDetail.sql" />
    <Build Include="dbo\Tables\MarketProcessDetailHistory.sql" />
    <Build Include="dbo\Tables\MarketProcessModel.sql" />
    <Build Include="dbo\Tables\MarketProcessModelHistory.sql" />
    <Build Include="dbo\Tables\MarketProcessModelGroup.sql" />
    <Build Include="dbo\Tables\MarketProcessModelGroupHistory.sql" />
    <Build Include="dbo\Tables\DbPurgeTables.sql" />
    <Build Include="dbo\Tables\MarketProcessProductGroup.sql" />
    <Build Include="dbo\Stored Procedures\usp_NonProductionDbUpdates.sql" />
    <Build Include="dbo\Stored Procedures\usp_NonProductionDbUpdates_LoanApplication.sql" />
    <Build Include="dbo\Stored Procedures\usp_NonProductionDbUpdates_ScoreSummary.sql" />
    <Build Include="dbo\Tables\ScoreProcessLogReasonType.sql" />
    <Build Include="dbo\Tables\ScoringProcessReasonLog.sql" />
    <Build Include="dbo\Tables\OverrideAPRMatrix.sql" />
    <Build Include="dbo\Tables\OverrideTermsOptions.sql" />
    <Build Include="dbo\Tables\OverrideAPRMatrixHistory.sql" />
    <Build Include="dbo\Tables\ExpirableAccount.sql" />
    <Build Include="dbo\Tables\ExpirableAccountHistory.sql" />
    <Build Include="dbo\Tables\ExpirableAccountProviderType.sql" />
    <Build Include="dbo\Tables\PasswordType.sql" />
    <Build Include="dbo\Tables\VendorDataPullConfig.sql" />
    <Build Include="dbo\Tables\VendorDataPullConfigDetail.sql" />
    <Build Include="dbo\Stored Procedures\usp_NonProductionDbUpdates_AgencyCertificate.sql" />
    <Build Include="dbo\Stored Procedures\usp_NonProductionDbUpdates_AgencyDetailAttribute.sql" />
  </ItemGroup>
  <ItemGroup>
    <None Include="SchemaComparisons\SchemaComparison.scmp" />
    <None Include="shared.publish.xml" />
  </ItemGroup>
  <ItemGroup>
    <PreDeploy Include="Scripts\Script.PreDeployment.sql" />
    <PostDeploy Include="Scripts\Script.PostDeployment.sql" />
    <None Include="package.nuspec">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <Private>true</Private>
    </None>
    <RefactorLog Include="CustomerScoringDatabase.refactorlog" />
  </ItemGroup>
  <ItemGroup>
    <Content Remove="Scripts\Script.PreDeployment.sql" />
    <Content Remove="Scripts\Script.PostDeployment.sql" />
  </ItemGroup>
  <ItemGroup>
      <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="150.0.0" DacpacName="master" DatabaseVariableLiteralValue= "master" />
  </ItemGroup>
</Project>

previously we used to do

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

Answer 3 · 2024-07-09T11:58:55.000Z

I need the project, not the resulting dacpac in order to help.

How are you deploying?

Answer 4 · 2024-07-09T12:06:25.000Z

using some custom code from a internal nuget package something like this

public async Task<(string, string, int)> DeployDacpacUsingSqlpackageAsync(
            string dacpacPath,
            string targetConnectionString,
            IDictionary<string, string>? variablesDictionary,
            int timeoutThreshold = 120_000,
            CancellationToken cancellationToken = default)
        {
            var argumentsBuilder = new StringBuilder($"/a:Publish /sf:\"{dacpacPath}\" /tcs:\"{targetConnectionString}\"");
            if (variablesDictionary != null)
            {
                foreach (var keyValuePair in variablesDictionary)
                {
                    _ = argumentsBuilder.Append($" /v:{keyValuePair.Key}={keyValuePair.Value}");
                }
            }

            Console.WriteLine("Spawning the SqlPackage.exe child process...");
            var processInfo = new ProcessStartInfo(FindSqlPackageExePath(), argumentsBuilder.ToString());
            processInfo.UseShellExecute = false;
            processInfo.RedirectStandardOutput = true;
            processInfo.RedirectStandardError = true;

            using var process = new Process();
            process.StartInfo = processInfo;

            var standardOutput = new StringBuilder();
            var standardError = new StringBuilder();
            process.OutputDataReceived += (sender, args) => standardOutput.AppendLine(args.Data);
            process.ErrorDataReceived += (sender, args) => standardError.AppendLine(args.Data);

            var stopwatch = new Stopwatch();
            stopwatch.Start();
            _ = process.Start();
            process.BeginOutputReadLine();
            process.BeginErrorReadLine();


            bool didExit;
            do
            {
                // Use Task.Delay(..) combined with checking the process.HasExited value so we don't have to block
                // the thread while we wait for the spawned process to complete
                await Task.Delay(2000, cancellationToken).ConfigureAwait(false);
                didExit = process.HasExited;
            }
            while (!didExit && stopwatch.ElapsedMilliseconds < timeoutThreshold && !cancellationToken.IsCancellationRequested);

            process.CancelOutputRead();
            process.CancelErrorRead();

            if (!didExit)
            {
                var timeoutException = new TimeoutException("The dacpac deploy process (SqlPackage.exe) did not complete within the timeout threshold");
                timeoutException.Data["TimeoutThreshold"] = timeoutThreshold;
                timeoutException.Data["STDOUT"] = standardOutput.ToString();
                timeoutException.Data["STDERR"] = standardError.ToString();
                throw timeoutException;
            }

            stopwatch.Stop();
            Console.WriteLine($"SqlPackage.exe process has finished. [EllapsedTime: {stopwatch.ElapsedMilliseconds} ms]");

            var exitCode = process.ExitCode;

            process.Close();

            if (exitCode != 0)
            {
                throw new DacpacDeployException(dacpacPath, exitCode, standardError.ToString(), standardOutput.ToString(), targetConnectionString);
            }

            return (standardOutput.ToString(), standardError.ToString(), exitCode);
        }

Answer 5 · 2024-07-09T12:07:31.000Z

I am trying to see if there is a good way I can create a public project which I can share I will check and I will let you know

Answer 6 · 2024-07-09T12:11:00.000Z

@bheemvennapureddy Great, just a small project that reproduces the error you see.

Answer 7 · 2024-07-09T12:12:57.000Z

You need the master.dacpac deployed alongside the new SDK .dacpac

Answer 8 · 2024-07-09T12:15:21.000Z

But with the way how we are referencing the master dacpac as the package reference that might not work no ?

Answer 9 · 2024-07-09T12:19:08.000Z

Your master .dacpac reference looks fine - again, with a repro project, I can actually confirm

Answer 10 · 2024-07-10T13:10:11.000Z

@ErikEJ i created a repo for reference but its not runnable on CI - which I am working on https://github.com/CuroFinTechCorp/TestMsbuildSDK but in the mean time if you would like to take a look

Answer 11 · 2024-07-10T13:14:05.000Z

the major difference i saw from what we currently have vs whats in new implementation is the File name on the model.xml part of the dacpac has the file name diff

with current implementation

		<CustomData Category="Reference" Type="SqlSchema">
			<Metadata Name="FileName" Value="C:\PROGRAM FILES\MICROSOFT VISUAL STUDIO\2022\ENTERPRISE\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\150\SQLSCHEMAS\MASTER.DACPAC" />
			<Metadata Name="LogicalName" Value="master.dacpac" />
			<Metadata Name="ExternalParts" Value="[master]" />
			<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
		</CustomData>

New SDK Style

	<Header>
		<CustomData Category="Reference" Type="SqlSchema">
			<Metadata Name="FileName" Value="master.dacpac" />
			<Metadata Name="LogicalName" Value="master.dacpac" />
			<Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
			<Metadata Name="ExternalParts" Value="[master]" />
		</CustomData>
	</Header>

Answer 12 · 2024-07-10T13:16:50.000Z

Correct, but it should still work, as you can see here: https://github.com/rr-wfm/MSBuild.Sdk.SqlProj/tree/master/test/TestProjectWithMasterReference - waiting for your repro

Answer 13 · 2024-07-10T13:20:13.000Z

ya i get that i have the repo created just making it reproduce the error on the ci side - I need to reference the local nuget package which I am not really successful so far

Answer 14 · 2024-07-10T13:20:21.000Z

@bheemvennapureddy Runnable on CI is not important, as long as it can be published with sqlpackage CLI, which is waht you "custom code" does anyway

Answer 15 · 2024-07-10T13:21:19.000Z

if you want to publish with sqlpackage cli I think the above repo should be ready

Answer 16 · 2024-07-10T13:22:19.000Z

OK, I will have a look

Answer 17 · 2024-07-10T13:26:55.000Z

I assume the master branch?

Answer 18 · 2024-07-10T13:50:43.000Z

I am lookin at the TestSdk branch now, and there are multiple issue:

The project file should be a .csproj, not a .sqlproj
You should remove package.nuspec, if this is the main .dacpac you want to publish
You .sqlproj does not conform to the format described in the readme here, it should look something like this:

<Project Sdk="MSBuild.Sdk.SqlProj/2.7.2">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <SqlServerVersion>Sql150</SqlServerVersion>
    <SuppressTSqlWarnings>71558,71502</SuppressTSqlWarnings>
    <TargetName>CustomerScoringDatabase</TargetName>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>
  <ItemGroup>
    <None Include="SchemaComparisons\SchemaComparison.scmp" />
    <None Include="shared.publish.xml" />
  </ItemGroup>
  <ItemGroup>
    <PreDeploy Include="Scripts\Script.PreDeployment.sql" />
    <PostDeploy Include="Scripts\Script.PostDeployment.sql" />
    <None Include="package.nuspec">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <Private>true</Private>
    </None>
  </ItemGroup>
  <ItemGroup>
    <Content Remove="Scripts\Script.PreDeployment.sql" />
    <Content Remove="Scripts\Script.PostDeployment.sql" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="150.0.0" DacpacName="master" />
  </ItemGroup>
</Project>