SQL Scripts are not included in new sqlproj file by default

[justintoth]

• SqlPackage or DacFx Version: 2.0.0-preview.1 (but I also tried 1.0.0)
• .NET Framework (Windows-only) or .NET Core: .NET 8.0.201
• Environment (local platform and source/target platforms): Windows 11 for ARM in Parallels w/Macbook Pro host

Steps to Reproduce:

1. Create a new SQL project by running: dotnet new -i Microsoft.Build.Sql.Templates and then dotnet new sqlproj -n TestSqlProj
2. Open the SQL project in Visual Studio 2022 or 2022 Preview.
3. Copy SQL scripts into that directory.
4. Notice that they don't show up in Visual Studio unless entries are manually added to the sqlproj file or the scripts are added using the Visual Studio UI.

sqlproj file that doesn't show any SQL scripts in the project directory:

<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="2.0.0-preview.1" />
  <PropertyGroup>
    <Name>TestSqlProj</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <ProjectGuid>{c38b164b-e4df-4154-9dcc-70a72f13eb93}</ProjectGuid>
  </PropertyGroup>
  <ItemGroup>
    <PostDeploy Include="Post-Deployment\PostDeploy.sql" />
  </ItemGroup>
  <ItemGroup>
    <Folder Include="Post-Deployment\" />
  </ItemGroup>
</Project>

sqlproj file with manual entries that does show the SQL scripts:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="2.0.0-preview.1" />
  <PropertyGroup>
    <Name>TestSqlProj</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <ProjectGuid>{41fd8a71-f200-4bc9-81df-defd9c11e95b}</ProjectGuid>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
  </PropertyGroup>
  <ItemGroup>
    <Folder Include="dbo\" />
    <Folder Include="dbo\Tables\" />
    <Folder Include="testschema\" />
    <Folder Include="testschema\Tables\" />
    <Folder Include="dbo\Stored Procedures\" />
    <Folder Include="Security\" />
    <Folder Include="Scripts\" />
  </ItemGroup>
  <ItemGroup>
    <Build Include="dbo\Tables\AppSetting_1.sql" />
    <Build Include="testschema\Tables\TestTable_1.sql" />
    <Build Include="dbo\Stored Procedures\AppSettingSelect.sql" />
    <Build Include="dbo\Stored Procedures\TestDacpacSelect.sql" />
    <Build Include="Security\testschema_1.sql" />
    <None Include="Scripts\ScriptsIgnoredOnImport.sql" />
  </ItemGroup>
</Project>

This contradicts in the documentation where it says that these entries are no longer needed.

[zijchen]

You need the SSDT extension that supports SDK-style projects: https://learn.microsoft.com/en-us/sql/ssdt/sql-server-data-tools-sdk-style?view=sql-server-ver16

[justintoth]

@zijchen Thanks for the tip. Unfortunately, after uninstalling SQL Server Data Tools and installing the SQL Server Data Tools SDK-style (Preview), when I attempt to open the sqlproj in Visual Studio 2022 Preview it says it's incompatible.

[justintoth]

I deleted my test sql project and uninstalled Microsoft.Build.Sql.Templates, then reinstalled it and created a new ProductsTutorial sql project and I was able to open it. However, building gives an error.

Error loading lock file 'C:\Dev\SqlProj\ProductsTutorial\obj\project.assets.json' : Error reading '' : Imports contains an invalid framework: 'unsupported' in 'project.json'.

[zijchen]

That error should be fixed in 2.0.0-preview.1, can you give that a try?

[justintoth]

I can confirm that version 2.0.0-preview.1 resolves the issue, thanks!