How to tell SqlPackage.exe to create object existence check script?

I have this code that creates a migration script for me:
First Part (Extract)

sqlpackage /a:extract 
           /of:true 
           /scs:"Data Source =.; Initial Catalog = Accounts; user id = sqluser; password = sqluser123;" 
           /tf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac

Second Part (DepolyReport)

sqlpackage /a:deployreport 
           /op:C:\Temp\SchemaCompare\Shop\Accounts\Report.xml 
           /of:True 
           /sf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac 
           /tcs:"Data Source=.;Initial Catalog=Shop;user id=sqluser;password=sqluser123;" 
           /P:DropObjectsNotInSource=True 
           /P:DropPermissionsNotInSource=False 
           /P:IgnorePermissions=True 
           /mp:10

Third Part (Script)

sqlpackage /a:script 
           /op:C:\Temp\SchemaCompare\Shop\Accounts\Migration.sql 
           /of:True 
           /sf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac 
           /tcs:"Data Source=.;Initial Catalog=Shop;user id=sqluser;password=sqluser123;" 
           /P:DropObjectsNotInSource=True 
           /P:DropPermissionsNotInSource=False 
           /P:IgnorePermissions=True 
           /mp:10

This works well. But it does not create the object existence check script.

For example, it creates this script:

CREATE TABLE [accounts].[Users] (
    [Id]                   BIGINT             IDENTITY (1, 1) NOT NULL,
    [UserName]             NVARCHAR (256)     NULL,
    [NormalizedUserName]   NVARCHAR (256)     NULL,
    [Email]                NVARCHAR (256)     NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC)
);

But I want it to create this script:

```
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounts].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [accounts].Users NOT NULL,
[UserName] nvarchar NULL,
[NormalizedUserName] nvarchar NULL,
[Email] nvarchar NULL,
C

SqlPackage follows a state-based database deployment paradigm. That means

• you define a source (Source.dacpac, which you derived from a database named "Accounts")

• you define the target (which you defined as a database named "Shop")

• SqlPackage is responsible for identifying the differences (this is what you see in the DeployReport step)

• SqlPackage is responsible for generating a script to bring the target in line with the source

You won't see those "IF EXISTS" type of checks on objects, because SSDT generates the script based on the state of the target database. In other words, it already knows if [accounts].[Users] exists and generates the script accordingly (to create it, alter it, or ignore it if it already matches the source dacpac).

This approach is not very tolerant of an environment where changes to the target might be made between when the script was generated and when it was run.

The only time I can recall seeing an existence check in these scripts is when the database itself is being created for the first name - in which case the script will check to see if a database by the requested name already exists.

StackExchange Database Administrators Q#303877, answer score: 4