Using SSDT Publish (.dacpac) to deploy non null foreign key schema updates

Question

I've been using .dacpacs to deploy database updates to our various environments. I have found a scenario that is causes the the publishing of a particular update to fail.

I need to add a new table dbo.Supplier and add a column to another table dbo.PickZone that has a non nullable foriegn key referencing the new table. The schema within the SSDT project reflects this, and to prepare for new NOT NULL column, i have the following pre-deployment script;

IF object_id('dbo.Supplier') IS NULL
BEGIN
    CREATE TABLE [dbo].[Supplier]
    (
        [SupplierId] INT IDENTITY(1,1) NOT NULL,
        [Name] varchar(50) NOT NULL,
        CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED ([SupplierId])
    );

    SET IDENTITY_INSERT [dbo].[Supplier] ON;
    INSERT INTO Supplier (SupplierId, Name) VALUES (1, 'Default Supplier')
    SET IDENTITY_INSERT [dbo].[Supplier] OFF;

    ALTER TABLE dbo.PickZone ADD SupplierId int NULL;

    UPDATE PickZone SET SupplierId = 1

END

The script above updates schema and data to ensure that when the publish occurs (i'm using sqlpackage.exe) that it will not fail when it applies the foriegn key constraint to dbo.PickZone.SupplierId:

CREATE TABLE [dbo].[PickZone]
(
    [PickZoneId] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY, 
    [Name] VARCHAR(50) NOT NULL,
    [SupplierId] INT NOT NULL,
    CONSTRAINT [FK_PickZone_Supplier] FOREIGN KEY ([SupplierId]) REFERENCES [dbo].[Supplier] ([SupplierId])
)

The problem, is that it seems both vs2012 Publish and sqlpackage.exe deploy prepares the schema updates required then executes the pre-deployment script, and then executes the schema updates - which are now out of sync due to the pre-deployment script making schema changes.

This causes the schema publish to again attempt to add the table and column, and results in it failing.

I can obviously change my deploy process to execute these types of preparation scripts outside the of the dacpac deploy, but I would kind of like the dacpac to be responsible for all schema changes...

Does anyone know a way to get dacpac publish to cater for this type of update?

Answer 1

I will describe my solution. We going to use sqlpackage tool which deploys dacpacs. This tool supports a lot of parameters. One of them is GenerateSmartDefaults. See here.

As I think pre-deployment script not must contain schema modifications. Only SELECT, INSERT, UPDATE, DELETE statements. Post-deployment script too.

1. Modify your db schema (add not null column without default or as foreign key).
2. In post-deployment script add UPDATE statement for this column and build dacpac.
3. Deploy this dacpac with SqlPackage tool like this: sqlpackage.exe /Action:Publish /SourceFile:your.dacpac /TargetServerName:serverName /TargetDatabaseName:databaseName /p:GenerateSmartDefaults=True