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

  • Article

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

BEGIN

EXEC @lock_result = sp_getapplock

@Resource = 'MS_ISServer_Create_Execution',

@LockTimeOut= 5000,

@LockMode = 'Exclusive'

SET @retry = @retry - 1

END

IF @lock_result < 0

BEGIN

RAISERROR(27195, 16, 1) WITH NOWAIT

END

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.