I've just upgraded my SSIS packages from SQL2005 to SQL2012 and I'm using SQL Agent to execute the pacakges. The jobs are failing about 20% of the time with a timeout error. I ran a trace to see what was causing the problem and the error occurs in the SSISDB.catalog.create_execution procedure when it calls sp_getapplock.
EXEC @lock_result = sp_getapplock
@Resource = 'MS_ISServer_Create_Execution',
@LockMode = 'Exclusive'
The timeout occurs in sp_getapplock when it calls sys.xp_userlock, the error number is 27195.
exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout
I have 11 packages that are all executed every minute so I suspect that this is 'causing' the problem. I had no issues when I execute 11 packages concurrently in SQL2005 or SQL2008.
Has anyone run into this problem? Do you have any idea how I can prevent this error other than scheduling the packages to run less frequently?