Hi @rehnerik I'm not able to reproduce this issue. I followed the steps to remove files from build but they are excluded as expected. Could you share a ZIP of a min-repro of your project? Thank you.

Hi @zijchen
After further testing it's actually the post deployment script that is failing the build.

My PostDeployment script looks like this

USE $(DatabaseName)

:r .\Users\somescript1.sql
:r .\Users\somescript2.sql
:r .\Users\somescript3.sql
:r .\Users\somescript4.sql
:r .\Users\somescript5.sql

I have the script specified as post depoly in sqlproj as described in the docs: Pre/post-deployment scripts that are specified by the PreDeploy or PostDeploy tags are automatically excluded from build.

Like this:
<PostDeploy Include="Script.PostDeployment.sql"/>

@zijchen I've updated first comment aswell.

@rehnerik everything you're describing seems to be correct, I'm not sure why they're still included in the build. Could you share your sqlproj file so I can take a look?

@zijchen

Here's sqlproj file:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
  <PropertyGroup>
    <Name>Issue103</Name>
    <ProjectGuid>{8E29F6E6-7263-4B44-9C26-D20634E15492}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
  <ItemGroup>
    <PostDeploy Include="Script.PostDeployment.sql" />
  </ItemGroup>
  <ItemGroup>
    <Build Remove="Users\somescript1.sql" />
    <Build Remove="Users\somescript2.sql" />
    <Build Remove="Users\somescript3.sql" />
    <Build Remove="Users\somescript4.sql" />
    <Build Remove="Users\somescript5.sql" />
  </ItemGroup>
</Project>

I am able to reproduce this now, thanks for all the details.

The problem seems to be dotnet build picking up the files that are specified as :r script.sql in the post deployment script.

I tried this in SSDT and it works, so this is specific to the SDK. Looking into this...

@zijchen Yes it works in SSDT. Thanks for looking into this.

@zijchen A lot of work went into fixing this in your "sister" project: rr-wfm/MSBuild.Sdk.SqlProj@01929bd

+1 I'm running into this issue now trying to convert to the SDK as well

FYI, having a similar issue here. if you need another example.

I have those additional scripts setup as though because that's how it was setup by ssdt
oddly, I just now tried (didn't know you could do that....) and that worked but the files were now absent from the "project explorer".

putting both and does exactly what I would expect coming from a ssdt world (files in the project explorer, build succeeds and the additional scripts included in the "final" PostDeploy.sql file inside the DacPac

@zijchen Any news on this issue?

I haven't had a chance to look into this yet, but I'll prioritize this as it appears to be a common issue.

Ok here is the behavior I'm observing. I think the parsing of pre/post-deployment scripts is supposed to happen, we have some 72xxx error codes that are specific for pre/post scripts.

There are some buggy behavior I am observing around when they get parsed though:

• When the included scripts are at the project root, they aren't parsed (this seems like a bug).
• When the included scripts are in a folder, they get parsed and errors are emitted if there are invalid syntax or SQLCMD variables that are not added to the project.

I am able to reproduce the above with both SDK-style and in SSDT 2019/2022 too, so I don't think this is limited to the SDK.

Ok here is the behavior I'm observing. I think the parsing of pre/post-deployment scripts is supposed to happen, we have some 72xxx error codes that are specific for pre/post scripts.

There are some buggy behavior I am observing around when they get parsed though:

• When the included scripts are at the project root, they aren't parsed (this seems like a bug).
• When the included scripts are in a folder, they get parsed and errors are emitted if there are invalid syntax or SQLCMD variables that are not added to the project.

I am able to reproduce the above with both SDK-style and in SSDT 2019/2022 too, so I don't think this is limited to the SDK.

Actually this is not entirely true. I think there's a bug with our batch parser. It seems to be ignoring any invalid syntax at the beginning of any batch, so the ordering of the included scripts made a difference.

At sqlproj build time, any file included by SQLCMD :r are read, parsed, and added to the postdeploy.sql in the DACPAC recursively. If you unzip a dacpac and inspect it, you will notice all the :r are replaced by the script contents.

The parsing that happens at this step will generate the errors seen above, @rehnerik the 72008 error from your original post should go away if you add x as a SQLCMD variable to the project.

The batch parser error I'm seeing ignores invalid syntax until a valid token, which meant the ordering of the included scripts mattered. This led me to assume there was a bug where root level scripts aren't being parsed correctly.

If I have the following T-SQL in an included script, build passes:

alsdjflajioewo
CREATE TABLE Table1 (Col1 INT NULL)

This also works:

USE [$(DatabaseName)]
GO

aweijoawjfoiwao
CREATE TABLE Table1 (Col1 INT NULL)
GO

But this generates error (expected):

CREATE TABLE Table1 (Col1 INT NULL)
oweiafjiojefaj

@zijchen Any updates on this issue?

@NenoLoje Do you have a sample project that reproduces the issue? I believe the original issue was due to misconfigured SQLCMD variables.

Also the issue with parser ignoring invalid syntax is incorrect too. The parser treats single-token strings as identifiers, as they could be a stored procedure being executed, hence why no error is thrown.

A note for anyone reading this thread, you can simplify pre- and post-build script exclusions outlined in the OP with this:

<Build Remove="Post-Deployment\*.sql" />
<None Include="Post-Deployment\*.sql" />

Fun fact, if you change the file extension from .dacpac to .zip, you can extract the contents and use BeyondCompare to see if there are discrepancies in the output scripts.