SQLPackage publish is not working in linux, dacpac built with sqlproject have absolute path to referenced dacpacs

[JLLange]

Hello,

I am trying to use sqlpackage publish to deploy a dacpac created by MSBuild.Sdk.SqlProj and having issues with the absolute path.
details...

I have a csProj sql project that references another DACPAC as a nuget.

this is the nuget reference line from the project

<PackageReference Include="SomeDBDacpacPackageName" Version="5.84.0" DacpacName="SomeDB"></PackageReference>
I copy the two dacpacs from the bin folder after building the cs project file.

The files are coppied into a docker linux session and I cd to that directory and call sqlpackage.

/tmp/sqlpackage/sqlpackage /Action:Publish /TargetServerName:localhost /TargetDatabaseName:SomeDB /TargetUser:TargetUser /TargetPassword:TargetPassword /SourceFile:My.DatabaseDacpac.dacpac /p:ScriptDatabaseOptions=false /TargetTrustServerCertificate:true /rp:/tmp/dacpacs/EDB/Libraries/SomeDb.dacpac

I have tried with and without /rp, doesn't help.

#9 14.12 Initializing deployment (Start)
#9 14.82 *** No file was supplied for reference SomeDB.dacpac; deployment might fail. When package was created, the original referenced file was located C:\Users\UserName.nuget\packages\SomeDBDacpacPackageName\5.84.0/tools/SomeDB.dacpac.
#9 14.82 Initializing deployment (Failed)
#9 15.83 *** An error occurred during deployment plan generation. Deployment cannot continue.
#9 15.83 Error SQL0: The reference to external elements from the source named 'SomeDB.dacpac' could not be resolved, because no such source is loaded. fail. When package was created, the original referenced file was located C:\Users\UserName.nuget\packages\SomeDBDacpacPackageName\5.84.0/tools/SomeDB.dacpac.
#9 14.02 Initializing deployment (Failed)
#9 15.24 *** An error occurred during deployment plan generation. Deployment cannot continue.
#9 15.24 Error SQL0: The refere

Is there anyway to get the csproj to build with relative paths?
has anyone successfully deployed in linux with a dacpac that references another dacpac? If so how?

Thanks for your time

[JLLange]

I added /Diagnostics and found that using rp does direct sqlpackage publish to the folder containing the dacpac, however it is appending part of the nuget path to the file name...
Searching for file 5.84.0/tools/SomeDB.dacpac using reference path /tmp/dacpacs/EDB/Libraries

[JLLange]

this appears to be a behavior difference in the execution of sqlpackage between windows and linux,
When executed in linux the file name includes the path from the nuget reference.

linux
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2024-04-02T16:20:58 : Searching for file 5.84.0/tools/TCEnterpriseDB.dacpac using relative path /tmp/dacpacs/EDB/Libraries

windows
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2024-04-02T16:43:15 : Searching for file TCEnterpriseDB.dacpac using reference path C:\Users\Jeremy.Lange_temp\Libraries

[ErikEJ]

@JLLange Are you able to provide a repro of this, then I will investigate

[ErikEJ]

This looks related: microsoft/DacFx#329

[JLLange]

I agree, the issue appears related. Fixing it might be enough to fix the problem I am seeing too.
I am working on something else at the moment and hope to get back to this soon.
But from memory...

I have a MS Build SDK Sql project that references another DACPAC.
The DACPAC is referenced as a NuGet package.
Build the DB project and grab the created DACPAC and referenced DACPAC from release folder.
Start up a MS SQL Server Linux instance in Docker.
Copy the files into the Linux session.
Try and deploy the DACPAC.
Here is the publish line from the shell script I am using.
/tmp/sqlpackage/sqlpackage /Action:Publish /TargetServerName:localhost /TargetDatabaseName:$dbName /TargetUser:sa /TargetPassword:$pswd /SourceFile:$dacpac /p:ScriptDatabaseOptions=false /TargetTrustServerCertificate:true $optionsString

[ErikEJ]

@JLLange Which MsBuild.SDK.SqlProj version?? (look at your .csproj files)

[JLLange]

Sql150

[ErikEJ]

No, the first line in your .csproj file, pls

[JLLange]

sorry...
<Project Sdk="MSBuild.Sdk.SqlProj/2.5.0">

[ErikEJ]

Latest version is 2.7.2! 2.5.0 is not supported any longer

[JLLange]

We are actively updating them. Do you think the issue is fixed?

[ErikEJ]

Not sure, but great that you are investigating. (You can always downgrade if required)

[bheemvennapureddy]

i am running into similar issue when using master dacpac on mac

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.

[bheemvennapureddy]

sqlproj used 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" />
  </ItemGroup>
</Project>

[bheemvennapureddy]

We used do this previously

<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>

[ErikEJ]

@bheemvennapureddy Please create a new issue with a full runnable repro project attached.

[ErikEJ]

@JLLange Any news?

[JLLange]

We have a story to update one of the project's that cause the issue, but it might be a sprint or two before we get to it.

[ErikEJ]

@JLLange any news?

[JLLange]

sorry not yet, still on the to do list and not forgotten about

[ErikEJ]

@jmezach @JLLange A fix for this was included in the next version of DacFX 162.4 - so will be included in next version of this SDK

[JLLange]

Great to hear, I'll keep an eye out for it, thank you.