SqlPackage produces huge deployment script and diff report for inmemory procs and functions

[IVNSTN]

• SqlPackage or DacFx Version: 162.2.111.2
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):
  • VS Build Tools 16.11.28
  • sqlpackage 15.0.5274.2
  • Windows 10

Steps to Reproduce:

1. Create in-memory DB project, add procs, functions with BEGIN ATOMIC block
2. Build dacpac in VS 2019 or using VS Build Tools on CI server
3. Deploy this dacpac using the latest sqlpackage.exe
4. Deploy it again and see all the natively compiled procs and functions with BEGIN ATOMIC block in the publish script again as if they were modified (whilst they were not)

This diff is permanent and cannot be fixed by redeployment.

We've made some research and noticed that there is a strange thing in dacpacs:

<Relationship Name="FunctionBody">
    <Entry>
        <Element Type="SqlScriptFunctionImplementation">
            <Property Name="BodyScript">
                <Value><![CDATA[ SNAPSHOT, LANGUAGE = N'English')
...
END;]]></Value>
            </Property>
            <Annotation Type="SysCommentsObjectAnnotation">
                <Property Name="Length" Value="1870" />
                <Property Name="StartLine" Value="1" />
                <Property Name="StartColumn" Value="1" />
                <Property Name="HeaderContents" Value="CREATE FUNCTION dbo.foo (@dt DATE)&#xD;&#xA;RETURNS BIT&#xD;&#xA;WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER&#xD;&#xA;AS&#xD;&#xA;BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL =" />
            </Annotation>
        </Element>
    </Entry>
</Relationship>

see this SNAPSHOT, LANGUAGE = N'English' piece of header placed into body block instead of HeaderContents property value. I believe this is related to this bug in ScriptDom which has been fixed long ago. However this fix was not published as an update to SSDT for VS 2019 and I don't understand how to deliver this fix to our CI/CD pipeline.

There is a page in SSDT documentation describing how to alter DacFx version used by SSDT. But if I understand it correctly it states that DacFx version can be updated only within the same major version number. VS Installer says that there is no update for SSDT component and it has DacFx version 15.0. As far as I understand I cannot put 162. or 164. preview version of DacFx into SSDT installation folder. Thus we have very old DacFx version for building dacpac files via MSBuild and one of the latest versions of DacFx for deployment of dacpacs via sqlpackage.exe.

The old version does contain described ScriptDom bug and the new one does not. I guess this is the reason why sqlpackage treats all the BEGIN-ATOMIC-procs as always different - there is a piece of garbage in the XML representation of SP body in dacpac file.

And we cannot migrate to SDK-style projects since it is not fully supported by VS and currently is in preview only. If we could, I believe the issue would be fixed since the build process started using the latest DacFx nuget package.

Is it possible to issue SSDT release for VS 2019 with the latest DacFx version? If not, then is there a workaround to utilize latest DacFx version for building SSDT-style projects?

We don't use VS 2022 but I'd like to know if it provides SSDT version with the latest DacFx library.

Did this occur in prior versions? If not - which version(s) did it work in?

I assume this permanent diff appeared after updating sqlpackage to the first version where fixed ScriptDom version was included. Before that both SSDT-DacFx and SqlPackage-DacFx contained the same bug and if I'm not mistaken there was no such permanent diff caused by BEGIN-ATOMIC block.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[ErikEJ]

@IVNSTN Why not switch to latest VS, the latest preview already contains 162.2 and can be updated if needed

[ErikEJ]

@IVNSTN There is also the option of using MsBuild.Sdk.SqlProj is xplat and updated depedencies matter to you

[dzsquared]

Is it possible to issue SSDT release for VS 2019 with the latest DacFx version?

VS 2019 will not recieve updates out of the DacFx/SqlPackage 150 version band.

If not, then is there a workaround to utilize latest DacFx version for building SSDT-style projects?

To continue using the original .sqlproj, adopting the community MsBuild.Sdk.SqlProj is the only option I can think of other than updating to VS 2022. Updating to VS 2022 is my recommendation., at least for SQL projects build.

[IVNSTN]

Okay, thank your for detailed response.

[IVNSTN]

Seems like migrating to 2022 Build Tools resolves the issue.