Execute SSDT project/dacpac deployment script in single transaction, including pre/post scripts

3
votes

I am using SQLPackage.exe utility together with Database project's publish profiles to run changes to the SQL Server database.

From my understanding, this is the deployment process:

Pre-Deployment Script

Main Deployment

Post-Deployment Script

I have "Include transactional scripts" option checked in the publish profile advanced settings, however, that seems to apply to Main Deployment section only.

My questions is: If Main deployment fails, is Pre-Deployment script committed? As well as, if Post-Deployment script fails, does Pre-Deployment changes and Main Deployment changes are being committed?

Is it possible to make publish script "Atomic" - Everything succeeds or fails as a single unit?

Thank you.

1
sql-serversql-server-data-toolsdatabase-projectdacpacsqlpackage
Good question. I have not found a way to make this happen. You can put pre-deployment scripts and post deployment scripts in thier own transaction but it will not rollback all the DDL it runs. - sean
Pre and post deploy scripts run outside of the rest of the transactions. You'll need to roll your own in some way. Ideally, running in a test environment and making sure there are no outside changes will probably be your best bet to ensure everything runs smoothly. - Peter Schott

1 Answers

4
votes

You should simply open the transaction in the pre script and commit it in the post script. It is very similar code like SSDT generates when you set this setting on.

Pre script:

IF (SELECT OBJECT_ID('tempdb..#tmpErrors2')) IS NOT NULL DROP TABLE #tmpErrors2
GO
CREATE TABLE #tmpErrors2 (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO



--Your prescript part goes here



GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors2 (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END

Post script:

GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors2 (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO

IF EXISTS (SELECT * FROM #tmpErrors2) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'Post/Pre script finished'
COMMIT TRANSACTION
END
ELSE PRINT N'Pre/Post scripts failed'
GO
DROP TABLE #tmpErrors2
GO