[Question]: Does internaldacpacs needs to be under tools folder referenced in other projects

[bheemvennapureddy]

Here is my current csproj file which fails with Error SQL46010: Incorrect syntax near ].

<?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>WinchkDatabase</TargetName>
    <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>

  <ItemGroup>
    <None Include="SchemaComparisons\SchemaComparison.scmp" />
    <None Include="shared.publish.xml" />
    <None Include="SchemaComparisons\FromCashMoneyToProject.scmp" />
    <None Include="SchemaComparisons\FromSRC18_06.scmp" />
    <None Include="shared.publish.xml" />
    <None Include="local.publish.xml" />
    <None Include="SchemaComparisons\FromSrcToProject.scmp" />
    <None Include="SchemaComparisons\FromProjectToLocalDb.scmp" />
    <None Include="SchemaComparisons\FromCM18_06.scmp" />
  </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" />
    <Content Remove="Scripts\Post-Deployment\2429.0-PostDeployment.sql" />
    <Content Remove="Scripts\Pre-Deployment\2429.0-PreDeployment.sql" />
    <RefactorLog Include="WinchkDatabase.refactorlog" />
  </ItemGroup>
  <ItemGroup>
    <SqlCmdVariable Include="AccountingDatabase">
      <DefaultValue>AccountingDatabase</DefaultValue>
      <Value>$(SqlCmdVar__8)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="AppLogDatabase">
      <DefaultValue>AppLogDatabase</DefaultValue>
      <Value>$(SqlCmdVar__15)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="BankCard">
      <DefaultValue>BankCard</DefaultValue>
      <Value>$(SqlCmdVar__13)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="CAPSHistoryDatabase">
      <DefaultValue>CAPSHistory</DefaultValue>
      <Value>$(SqlCmdVar__14)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="CustomerScoringDatabase">
      <DefaultValue>CustomerScoring</DefaultValue>
      <Value>$(SqlCmdVar__6)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="InternalAuditDatabase">
      <DefaultValue>InternalAudit</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SqlReportServer">
      <DefaultValue>SQLRPT</DefaultValue>
      <Value>$(SqlCmdVar__9)</Value>
    </SqlCmdVariable>
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="150.1.1" DacpacName="master" DatabaseVariableLiteralValue= "master" />
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.Msdb" Version="150.0.0" DacpacName="msdb" DatabaseVariableLiteralValue= "msdb"/>
    <PackageReference Include="Database.AppLog" Version="2429.0.2" DacpacName="CustomerScoringDatabase"/>
    <PackageReference Include="Database.CustomerScoring" Version="2429.0.1" DacpacName="AppLogDatabase" />
    <PackageReference Include="Database.InternalAudit" Version="2429.0.1" DacpacName="InternalAuditDatabase"/>
    <PackageReference Include="Database.CAPSHistory" Version="2429.0.2" DacpacName="CAPSHistoryDatabase"/>
    <PackageReference Include="Database.HotelCardifornia" Version="1.0.*" DacpacName="HotelCardifornia.Database"/>
  </ItemGroup>
</Project>

it fails while adding an internaldacpac to the model

     1>PrepareForBuild:
         Creating directory "bin\Any CPU\SqlConvert\".
         Creating directory "obj\Any CPU\SqlConvert\".
##[warning]src\WinchkDatabase\WinchkDatabase.sqlproj(0,0): Warning NU1604: Project dependency Database.AppLog does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
     1>D:\a\1\s\src\WinchkDatabase\WinchkDatabase.sqlproj : warning NU1604: Project dependency Database.AppLog does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
##[warning]src\WinchkDatabase\WinchkDatabase.sqlproj(0,0): Warning NU1604: Project dependency Database.CAPSHistory does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
     1>D:\a\1\s\src\WinchkDatabase\WinchkDatabase.sqlproj : warning NU1604: Project dependency Database.CAPSHistory does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
##[warning]src\WinchkDatabase\WinchkDatabase.sqlproj(0,0): Warning NU1604: Project dependency Database.CustomerScoring does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
     1>D:\a\1\s\src\WinchkDatabase\WinchkDatabase.sqlproj : warning NU1604: Project dependency Database.CustomerScoring does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
##[warning]src\WinchkDatabase\WinchkDatabase.sqlproj(0,0): Warning NU1604: Project dependency Database.InternalAudit does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
     1>D:\a\1\s\src\WinchkDatabase\WinchkDatabase.sqlproj : warning NU1604: Project dependency Database.InternalAudit does not contain an inclusive lower bound. Include a lower bound in the dependency version to ensure consistent restore results.
       ResolveDatabaseReferences:
         Resolved dacpac file from package NETStandard.Library to d:\a\.nuget\restore\tiger\6.2.1netstandard.library/2.0.3/tools/NETStandard.Library.dacpac
         Resolved dacpac file from package Microsoft.SqlServer.Dacpacs.Master to D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.master\150.1.1/tools/master.dacpac
         Resolved dacpac file from package Microsoft.SqlServer.Dacpacs.Msdb to D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.msdb\150.0.0/tools/msdb.dacpac
         Resolved dacpac file from package Database.AppLog to d:\a\.nuget\restore\tiger\6.2.1database.applog//tools/Database.AppLog.dacpac
         Resolved dacpac file from package Database.CustomerScoring to d:\a\.nuget\restore\tiger\6.2.1database.customerscoring//tools/Database.CustomerScoring.dacpac
         Resolved dacpac file from package Database.InternalAudit to d:\a\.nuget\restore\tiger\6.2.1database.internalaudit//tools/Database.InternalAudit.dacpac
         Resolved dacpac file from package Database.CAPSHistory to d:\a\.nuget\restore\tiger\6.2.1database.capshistory//tools/Database.CAPSHistory.dacpac
         Resolved dacpac file from package Database.HotelCardifornia to d:\a\.nuget\restore\tiger\6.2.1database.hotelcardifornia/1.0.*/tools/Database.HotelCardifornia.dacpac
         Resolved database package references: Microsoft.SqlServer.Dacpacs.Master;Microsoft.SqlServer.Dacpacs.Msdb
       ValidateEnvironment:
         Using target framework net6.0 to run DacpacTool
       CoreCompile:
         dotnet "D:\a\.nuget\cache\Tiger\6.2.1\msbuild.sdk.sqlproj\2.7.2\Sdk\../tools/net6.0/DacpacTool.dll" build -o "obj\Any CPU\SqlConvert\WinchkDatabase.dacpac" -n "WinchkDatabase" -v "2429.0.3" -sv Sql150 -i "obj\Any CPU\SqlConvert\WinchkDatabase.InputFiles.txt" -r "D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.master\150.1.1/tools/master.dacpac;dbl=master|dbv=|srv=;" -r "D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.msdb\150.0.0/tools/msdb.dacpac;dbl=msdb|dbv=|srv=;" -sc AccountingDatabase="AccountingDatabase" -sc AppLogDatabase="AppLogDatabase" -sc BankCard="BankCard" -sc CAPSHistoryDatabase="CAPSHistory" -sc CustomerScoringDatabase="CustomerScoring" -sc InternalAuditDatabase="InternalAudit" -sc SqlReportServer="SQLRPT"  -dp IncludeCompositeObjects=true --predeploy Scripts\Script.PreDeployment.sql --postdeploy Scripts\Script.PostDeployment.sql --refactorlog WinchkDatabase.refactorlog  -spw "71558,71502"      
         Using package name WinchkDatabase and version 2429.0.3
         Using SQL Server version Sql150
         Adding reference to D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.master\150.1.1/tools/master.dacpac with external parts dbl=master|dbv=|srv= and SuppressMissingDependenciesErrors False
         Adding reference to D:\a\.nuget\restore\Tiger\6.2.1\microsoft.sqlserver.dacpacs.msdb\150.0.0/tools/msdb.dacpac with external parts dbl=msdb|dbv=|srv= and SuppressMissingDependenciesErrors False
         Adding SqlCmd variable AccountingDatabase with default value AccountingDatabase
         Adding SqlCmd variable AppLogDatabase with default value AppLogDatabase
         Adding SqlCmd variable BankCard with default value BankCard
         Adding SqlCmd variable CAPSHistoryDatabase with default value CAPSHistory
         Adding SqlCmd variable CustomerScoringDatabase with default value CustomerScoring
         Adding SqlCmd variable InternalAuditDatabase with default value InternalAudit
         Adding SqlCmd variable SqlReportServer with default value SQLRPT
         Adding D:\a\.nuget\restore\Tiger\6.2.1\database.internalaudit\2313.0.3\contentFiles\any\any\databases\InternalAuditDatabase.dacpac to the model
         Unhandled exception. System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
          ---> Microsoft.SqlServer.Dac.Model.DacModelException: Add or update objects failed due to the following errors: 
##[error]EXEC(0,0): Error SQL46010: Incorrect syntax near ].
     1>EXEC : error SQL46010: Incorrect syntax near ]. [D:\a\1\s\src\WinchkDatabase\WinchkDatabase.sqlproj]

[bheemvennapureddy]

here is the log for the internal dacpac

Starting: Copy InternalAuditDatabase Files to: D:\a\1\a/InternalAuditDatabase
==============================================================================
Task         : Copy files
Description  : Copy files from a source folder to a target folder using patterns matching file paths (not folder paths)
Version      : 2.238.0
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/copy-files
==============================================================================
found 2 files
Cleaning target folder: D:\a\1\a/InternalAuditDatabase
Copying D:\a\1\s\src\InternalAuditDatabase\bin\Any CPU\SqlConvert\InternalAuditDatabase.dacpac to D:\a\1\a\InternalAuditDatabase\InternalAuditDatabase.dacpac
Copying D:\a\1\s\src\InternalAuditDatabase\shared.publish.xml to D:\a\1\a\InternalAuditDatabase\shared.publish.xml
Finishing: Copy InternalAuditDatabase Files to: D:\a\1\a/InternalAuditDatabase

Starting: NuGet pack
==============================================================================
Task         : PowerShell
Description  : Run a PowerShell script on Linux, macOS, or Windows
Version      : 2.239.1
Author       : Microsoft Corporation
Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/powershell
==============================================================================
Generating script.
========================== Starting Command Output ===========================
"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy Unrestricted -Command ". 'D:\a\_temp\f09aa4e4-c9d8-4518-9411-71657752fe9f.ps1'"
Attempting to build package from 'package.nuspec'.
Successfully created package 'D:\a\1\a/Packages/InternalAuditDatabase\Database.InternalAudit.2428.0.3.nupkg'.
Finishing: NuGet pack

[ErikEJ]

Need a runnable minimal repro as always if you want free support.

[bheemvennapureddy]

@ErikEJ How can i share the internal nuget packages if they are referred in the csproj ?

[bheemvennapureddy]

Happy to create a test repo - still very new to dotnet and still learning - Any help is appreciated

[ErikEJ]

Just share the .dacpacs in your repro, and I can point nuget to the folder they live in

[bheemvennapureddy]

is this good enough https://github.com/CuroFinTechCorp/TestWinChk

[ErikEJ]

@bheemvennapureddy Probably. What are the instructions to get the error to appear?

[ErikEJ]

Why does it say ".sqlproj" - this SDK is NOT for .sqlproj !?

src\WinchkDatabase\WinchkDatabase.sqlproj

[bheemvennapureddy]

Where do you see that - that could be a typo. If you build the csproj pointed to local packages you should see that error

[ErikEJ]

@bheemvennapureddy I see it in your first message, the log part.

[bheemvennapureddy]

thats a CI thing - ignore that - if you build the csproj you should see that error.

[ErikEJ]

Yoy do not follow the guidelines for creating a .dacpac NuGet package:

Adding D:\a\.nuget\restore\Tiger\6.2.1\database.internalaudit\2313.0.3\contentFiles\any\any\databases\InternalAuditDatabase.dacpac to the model The .dacpac file should be in the tools folder only in the .nupkg file

[bheemvennapureddy]

@ErikEJ you are not even giving me time to respond on the issue and you are closing it. Nuget package gets added to both folder

something like this

[bheemvennapureddy]

All the nuget packages added to the test repo has the dacpac in the tools folder as well.

[ErikEJ]

It should not be present in the contentfiles folder.

[bheemvennapureddy]

Can we keep the ticket open until the issue is resolved ?

[ErikEJ]

This is not a "ticket" and you are being offered free support!

[bheemvennapureddy]

Appreciate your help @ErikEJ

[bheemvennapureddy]

It should not be present in the contentfiles folder.

is there a way we can just look at tools folder @ErikEJ - we have so many cascading issues with removing contentFiles internally - i was looking around the code and couldn't find a place where the tools folder is considered vs others. i would like to hear your inputs and if there is a way we can have dacpacs in both places

[ErikEJ]

I honestly do not see us making any changes because of this. Also unsure if that is actually the root cause, but in order to help I really need a much smaller targeted repro.

[bheemvennapureddy]

When ever you get a chance can you check this repo ? https://github.com/CuroFinTechCorp/TestWinChk

[ErikEJ]

I did, and that is not a simple repro

[bheemvennapureddy]

Were you not able to see the same error on the dotnet build on that repo ?

[bheemvennapureddy]

@ErikEJ check this https://github.com/CuroFinTechCorp/TestWinChk/pull/1 i was able to reproduce this on Github Actions as well.

[ErikEJ]

Yes, and manually removing the contentFiles folder with NuGet explorer unbreaks the build (I get some unresolved reference errors that seem unrelated to this issue)

[bheemvennapureddy]

Is there a way we can make it look only at tools folder and ignore contentFiles ?

[ErikEJ]

@bheemvennapureddy No idea, but your .nupkg files does not follow the standard format that this SDK supports. Maybe @jmezach has advice / an opinion?

[bheemvennapureddy]

The SDK you developed is incredible, and we truly appreciate the excellent support and knowledge you share. We're currently migrating from existing SSDT-style projects to this SDK. However, we're facing challenges with internal packages referenced from ContentFiles. As we're in the midst of this migration process, we cannot remove ContentFiles references immediately. While we plan to eventually eliminate them, we need some assistance to navigate this transition.

Thank you once again for creating such an outstanding SDK and for the continuous support.

[jmezach]

To be honest I don't know why it would look in the contentFiles folder at all. That's not any logic in the SDK. We're only ever looking at .dacpac files in the tools folder.

A binary log of your build could be helpful (dotnet build /bl I think) as that would give us more insight into why this would be happening. But ultimately I think the problem is that the .dacpac in the contentFiles folder contains something that is not valid.

[bheemvennapureddy]

@jmezach it pretty much gives me the same log https://github.com/CuroFinTechCorp/TestWinChk/actions/runs/10027848308/job/27714039658?pr=1

[bheemvennapureddy]

here is the csproj of the refereced dacpac nuget package

<?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>InternalAuditDatabase</TargetName>
    <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
    <RunScriptsFromReferences>True</RunScriptsFromReferences>
  </PropertyGroup>

  <ItemGroup>
    <None Include="shared.publish.xml" />
    <None Include="SchemaComparisons\SchemaComparison.scmp" />
    <RefactorLog Include="InternalAuditDatabase.refactorlog" />
  </ItemGroup>

  <ItemGroup>
    <PreDeploy Include="Scripts\Script.PreDeployment.sql" />
    <PostDeploy Include="Scripts\Script.PostDeployment.sql" />
    <None Include="package.nuspec">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

  <ItemGroup>
    <Content Remove="Scripts\Script.PostDeployment.sql" />
    <Content Remove="Scripts\Script.PreDeployment.sql" />
    <Content Remove="Scripts\Post-Deployment\2429.0-PostDeployment.sql" />
    <Content Remove="Scripts\Pre-Deployment\2429.0-PreDeployment.sql" />
  </ItemGroup>

</Project>

[bheemvennapureddy]

Nuspec for reference

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2013/05/nuspec.xsd">
  <metadata>
    <id>Database.InternalAudit</id>
    <version>$version$</version>
    <authors>CURO</authors>
    <owners>CURO</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>InternalAudit Database schema.</description>
    <tags>internal audit database</tags>
    <contentFiles>
      <files include="any/any/databases/InternalAuditDatabase.dacpac" buildAction="Content" copyToOutput="true" />
      <files include="tools/InternalAuditDatabase.dacpac" buildAction="Content" copyToOutput="true" />
    </contentFiles>
  </metadata>
  <files>
    <file src="InternalAuditDatabase.dacpac" target="contentFiles/any/any/databases" />
    <file src="InternalAuditDatabase.dacpac" target="tools" />
  </files>
</package>

[jmezach]

@bheemvennapureddy If you run dotnet build /bl locally you should get a msbuild.binlog in the folder where you ran it.

[bheemvennapureddy]

@jmezach here is the artifact on github actions https://github.com/CuroFinTechCorp/TestWinChk/actions/runs/10028087629?pr=1

[bheemvennapureddy]

Can we keep the ticket open ?

[ErikEJ]

It not locked - yet.

[ErikEJ]

@jmezach @bheemvennapureddy I looked at obj\debug\TestWinChk.InputFiles.txt, and it is evident that the .dacpac file is treated as if it was a .sql script:

C:\Users\xxx\.nuget\packages\database.hotelcardifornia\1.0.2\contentFiles\any\any\databases\HotelCardifornia.Database.dacpac
dbo\Functions\DatabaseSelect.sql
dbo\Functions\FN_AACbExport_CleanCity.sql
dbo\Functions\FN_AACbExport_CleanCustFields.sql
dbo\Functions\FN_AACbExport_GetGenerationCode.sql
dbo\Functions\FN_AACbExport_StripGenerationCode.sql
dbo\Functions\FN_ADDRESS_CSZ.sql
dbo\Functions\FN_ADDRESS_LINE.sql
dbo\Functions\FN_ADDRESS_LINE_1.sql
...

[ErikEJ]

Updating the .nuspec in a package with contentFiles (change the buildAction) to this also unbreaks the build:

<files include="any/any/databases/HotelCardifornia.Database.dacpac" buildAction="None" copyToOutput="true" />

So like this:

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>Database.HotelCardifornia</id>
    <version>1.0.2</version>
    <authors>CURO</authors>
    <owners>CURO</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <projectUrl>https://github.com/CuroFinTechCorp/HotelCardifornia.git</projectUrl>
    <description>HotelCardifornia Database schema.</description>
    <tags>HotelCardifornia database</tags>
    <contentFiles>
      <files include="any/any/databases/HotelCardifornia.Database.dacpac" buildAction="None" copyToOutput="true" />
      <files include="tools/HotelCardifornia.Database.dacpac" buildAction="Content" copyToOutput="true" />
    </contentFiles>
  </metadata>
</package>

[bheemvennapureddy]

What does a buildAction none means ?

[ErikEJ]

LMGTFY: https://stackoverflow.com/questions/145752/what-are-the-various-build-action-settings-in-visual-studio-project-properties

[bheemvennapureddy]

@jmezach @ErikEJ Thanks a ton for the support and help and that recommendation helped us a lot during this migration.