Ssdt Post Deployment Scripts

I would like to ignore post deployment scripts after it has been deployed. How do you archive/remove a branch specific post deployment script after it has been deployed on producti

Solution 1:

What I used to do is to create log table and store all the executed scripts. This is the table structure:

CREATETABLE dbo.publish_script_logs
(
    script_name_id VARCHAR(255) NOTNULL
  , database_name  VARCHAR(255) NOTNULL
  , execution_time DATETIME2(7) NOTNULL
);

Then we created following scripts folder structure:

one_time_scripts
  initial_data_insert.sql
  ...
  postscript_all_together.sql
  prescript_all_together.sql
  ...
Script.PostDeployment1.sql
Script.PreDeployment1.sql

where initial_data_insert.sql is your needed script that is supposed to be executed on environment just once and pre\postscript_all_together.sql are the scripts where all these scripts are collected together. Build = Nonemust be set for all of these scripts. There is limitation - GO statement separator is not allowed in "one time scripts".

Now this is what will these 2 scripts will have inside for single script:

:SETVAR ScriptNameId ".\initial_data_insert"
GO
IF NOTEXISTS ( SELECT*FROM    [dbo].[publish_script_logs]
                WHERE   [Script_Name_Id] ='$(ScriptNameId)'AND [database_name] = DB_NAME()
                )
BEGINBEGIN TRY
    :r $(ScriptNameId)".SQL"
    INSERTINTO [dbo].[publish_script_logs]
    VALUES  ( '$(ScriptNameId)', DB_NAME() ,GETDATE() );
END TRY
BEGIN CATCH
    DECLARE@errVARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE@msgVARCHAR(MAX) ='One time script $(ScriptNameId).sql failed '+@err;
    RAISERROR (@msg, 16, 1);
END CATCH
END;
GO

And finally in the Script.PostDeployment1.sql and Script.PreDeployment1.sql files you'll have:

:r .\one_time_scripts\postscript_all_together.sql

and

:r .\one_time_scripts\prescript_all_together.sql