'Execution Timeout Expired error during DACPAC (SSDT) publish using DacServices.Deploy

We're randomly getting the below error when publishing a dacpac using DacServices.Deploy in C#. Along with tables, the Dacpac also has a post-deployment script which is the part which is raising the timeout error. We're publishing onto a new SQL Server 2014 database (hence, not an upgrade scenario). Will anyone suggest a solution please?

PS: This typically happens on the first attempt on a new server and then works perfectly on second attempt. The DacPac publish is part of our installer and installer failure makes a bad impression.

Error...

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

We've explicitly set a timeout but that doesnt seem to be picked up because the publish fails in a little over a minute whereas the timeout is set to be 600 seconds.

DacDeployer code...

                var dacOptions = new DacDeployOptions();
                dacOptions.IncludeCompositeObjects = true;
                dacOptions.IncludeTransactionalScripts = true;
                dacOptions.GenerateSmartDefaults = true;
                //Specify timeout in seconds to override the default 60
                dacOptions.CommandTimeout = 600;

                using (DacPackage dacpac = DacPackage.Load(dacpacName))
                {
                    dacServiceInstance.Deploy(dacpac, DBName, upgradeExisting: true, options: dacOptions);
                }

Info, Warning and Error logs returned by the event listener on DacServices.Deploy...

Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating DW_Control...
Creating [DW_INTERNAL]...
Creating [DW_INTERNAL].[DB_VERSIONS]...
Creating [DW_INTERNAL].[SCRIPT_LOG]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_LOG' has maximum length of 1102 bytes. For some combination of large values, the insert/update operation will fail.
Creating [DW_INTERNAL].[SCRIPT_MASTER]...
Creating [DW_INTERNAL].[SCRIPT_STATUS]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_STATUS' has maximum length of 1094 bytes. For some combination of large values, the insert/update operation will fail.
Creating [dbo].[ARCHIVE_STATUS]...
Creating unnamed constraint on ...
Creating [dbo].[FK_...]...
Creating [dbo].[VW_...]...
Creating [DW_INTERNAL].[ADD]...
Creating [DW_INTERNAL].[ADD_SCRIPT]...
Creating [DW_INTERNAL].[UPGRADE]...
Creating [dbo].[VW_AMOUNT_SUMMARY].[MS_Description]...
The transacted portion of the database update succeeded.
.Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Script execution error.  The executed script:
EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.02.00';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 40, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_P_DML.sql';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 30, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_R_DML.sql';

EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.07.00';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '1.17.02.00', @ToVersion = '1.17.07.00', @ApplyOrder = 60, @UpgrScriptFilepath = 'CTL_1.17.02.00_1.17.07.00_P_DML.sql';

EXECUTE DW_INTERNAL.ADD_SCRIPT @M
An error occurred while the batch was being executed.
Updating database (Failed)
Error details: Could not deploy package.
Error SQL72014: 

Stack Trace: at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
   at DacDeployer.Program.Main(String[] args)

Stored Procedure invoked...

CREATE PROCEDURE [DW_INTERNAL].[ADD_SCRIPT]
    @Module VARCHAR(30), 
    @FromVersion VARCHAR(20), 
    @ToVersion VARCHAR(20), 
    @ApplyOrder INT, 
    @UpgrScriptFilepath VARCHAR(1024) 

AS
BEGIN
    DECLARE @FileName VARCHAR(500);
    SET @FileName = RIGHT(@UpgrScriptFilepath, CHARINDEX('\', REVERSE(@UpgrScriptFilepath)) - 1);

    IF NOT EXISTS (SELECT 1 FROM [DW_INTERNAL].[SCRIPT_MASTER] WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and RIGHT([UPGR_SCRIPT_FILEPATH], CHARINDEX('\', REVERSE([UPGR_SCRIPT_FILEPATH])) - 1) = @FileName)
    BEGIN
        INSERT INTO [DW_INTERNAL].[SCRIPT_MASTER]
                   ([MODULE],
                    [FROM_VERSION],
                    [TO_VERSION],
                    [APPLY_ORDER],
                    [UPGR_SCRIPT_FILEPATH])
             VALUES
                   (@Module,
                   @FromVersion,
                   @ToVersion,
                   @ApplyOrder,
                   @UpgrScriptFilepath)
    END
    ELSE
    BEGIN
        UPDATE [DW_INTERNAL].[SCRIPT_MASTER]
        SET [APPLY_ORDER] = @ApplyOrder
        WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and [UPGR_SCRIPT_FILEPATH]=@UpgrScriptFilepath
    END
END


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source