SSIS execution time out error when using SSISDB stored procedure [catalog].[create_execution]

After SQL Server 2012 is released, SSISDB provides stored procedures to create SSIS package executions. There is one problem though. If too many package executions are created a the same time, you may get the following error

"The operation failed because the execution timed out"

The cause is in the stored procedure [catalog].[create_execution]. It throws the timeout error from the following code that places a 5 second timeout on the application exclusive lock acquisition. It retries twice.


WHILE @retry > 0 AND @lock_result < 0


EXEC @lock_result = sp_getapplock

@Resource = 'MS_ISServer_Create_Execution',

@LockTimeOut= 5000,

@LockMode = 'Exclusive'

SET @retry = @retry - 1


IF @lock_result < 0




The lock is released when the COMMIT TRANSACTION clause executes in the later part of the stored procedure.

If there are too many clients calling this stored procedure to create a package execution, error 27195 execution timeout out is reported.

The solution is to avoid creating so many package execution at the same time.

Comments (1)

  1. Bryan Hinton says:

    This is an unbelievably poor suggestion. If the stored procedure takes a long time because of poor SSISDB performance than the problem is a whole lot bigger than creating a lot of package executions at the same time and one of simple throughput.

Skip to main content