DacFx generates invalid publish script with wrong CREATE command order if temporal table and dacpac-dependency involved

[IVNSTN]

• SqlPackage or DacFx Version:
  • SSDT - 16.0.62205.05200
  • SqlPackage - 162.0.52.1
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):
  • Windows 10
  • VS 2019

Our projects with tests based on tSQLt framework are organized like this:

• my_proj.sqlproj
• my_proj_test.sqlproj in the same solution with dependencies
  • on my_proj with "Same database" option
  • and tSQLt.dacpac with "Same database" option
  • and publish option IncludeCompositeObjects=True

Thus after deployment of both my_proj and my_proj_test to the same database we have a DB ready for testing. Sometimes we deploy in our automation pipelines only the my_proj_test which contains (through dependencies and IncludeCompositeObjects=True) everything needed.

But these deployments sometimes fail with error "schema tSQLt does not exist" on statement CREATE TABLE tSQLt.<whatever>. And after some investigation I noticed that CREATE SCHEMA tSQLt actually exists in the publish script but is located after table creation in this schema. The error is reproducible but the scenario is a little bit complicated, I hope my description will be helpful.

Steps to Reproduce:

Sample project sources: wrong_command_order.zip. This project does not have my_proj + my_proj_test structure but is enough to reproduce the very same problem.

1. Create DB and dbo.my_table table from the given project, don't deploy tSQLt objects
2. Uncomment new_column line in the table script
3. Generate publish script using publish.xml from the project
4. See valid statement order (but don't deploy it) - if the table is not temporal no error occurs
5. Comment new_column line back and uncomment SYSTEM_VERSIONING line
6. Deploy table changes and still don't deploy tSQLt objects
7. Uncomment new_column line in the temporal table script
8. Generate publish script using publish.xml from the project
9. See invalid statement order - CREATE SCHEMA tSQLt statement is far behind the first object creation in this schema

The error occurs only if modified table is temporal. Something inside DacFx breaks the valid command order. And I guess it's not related to anything from tSQLt framework, it's just the dependency which is involved in minimal repro.

Example of the broken publish script (partial)

Scroll till the "THIS IS TOO LATE" line.

``` GO PRINT N'Altering Table [dbo].[my_table_history]...'; GO ALTER TABLE [dbo].[my_table_history] ADD [new_column] BIT NULL; GO PRINT N'Creating Table [tSQLt].[CaptureOutputLog]...'; GO -- tSQLt objects creation started CREATE TABLE [tSQLt].[CaptureOutputLog] ( [Id] INT IDENTITY (1, 1) NOT NULL, [OutputText] NVARCHAR (MAX) NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); GO PRINT N'Creating Table [tSQLt].[Private_Configurations]...'; GO CREATE TABLE [tSQLt].[Private_Configurations] ( [Name] NVARCHAR (100) NOT NULL, [Value] SQL_VARIANT NULL, PRIMARY KEY CLUSTERED ([Name] ASC) ); -- ... -- lots of tSQLt object creation statements here -- ... GO PRINT N'Altering Table [dbo].[my_table]...'; GO ALTER TABLE [dbo].[my_table] ADD [new_column] BIT NULL; GO PRINT N'Adding system-versioning to [dbo].[my_table]...'; GO ALTER TABLE [dbo].[my_table] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[my_table_history], DATA_CONSISTENCY_CHECK=ON)); GO PRINT N'Creating Schema [tSQLt]...'; GO -- THIS IS TOO LATE !!! CREATE SCHEMA [tSQLt] AUTHORIZATION [dbo]; GO PRINT N'Creating Assembly [tSQLtCLR]...'; GO CREATE ASSEMBLY [tSQLtCLR] AUTHORIZATION [dbo] FROM ... GO PRINT N'Creating User-Defined Type [tSQLt].[Private]...'; -- ... -- more tSQLt objects creation -- ... GO CREATE TYPE [tSQLt].[Private] EXTERNAL NAME [tSQLtCLR].[tSQLtCLR.tSQLtPrivate]; ... ```

As a workaround we create schema manually at target DB before deployment when the problem occurs.

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

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[IVNSTN]

And it looks like "temporal table involved" is not a requirement but this is the case I was able to build minimal repro for. Such issues happen from time to time in our deployments.