SSIS package fails to start - application lock timeout in SSISDB.catalog.create_execution
In short: when a scheduled job tries to execute an SSIS package , there's a time-out in the communication between the job and the SSISDB database. The package is never executed. The original bug reports was closed as duplicate (no duplicate to be found though).
I encountered this bug in SQL Server 2017, patched to the latest CU.
Error message: Failed to execute IS server package because of error 0x80131904. Description: The operation failed because the execution timed out. Source: .Net SqlClient Data
the link to connect is now deprecated
raghavender tuniki reddy commented
We have faced similar issues. Tried to re run the Package with the SQL agent Job it fails to pick up the Package while it working fine until now.
As the error message indicates the Job failed to connect to Package because of the missing reference ID= being 'NULL'
After researching around , we figured the reference id is gone when you actually edit the step to either see some details (or) actually do some changes and do 'OK'. this will actually make the JOB step to miss the reference ID.
So instead you should cancel the step when there is no change.
OR recreate the JOB if there is any change.
Jack Sprat commented
Same issue. A CDC SSIS package runs fine every 30 seconds for days on end, then starts failing with this error out of the blue. (SQL Server 13.04001.0) Usually a reboot of the server fixes it, but it is permanent this last failure.
Executed as user: XXXXXX. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 2:40:38 PM Failed to execute IS server package because of error 0x80131904. Server: XXXXXXXXXXX, Package path: \SSISDB\CDC Operations\CDC_Upsert\CDC IncrementalLoad.dtsx, Environment reference Id: NULL. Description: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Source: .Net SqlClient Data Provider Started: 2:40:38 PM Finished: 2:41:08 PM Elapsed: 30.125 seconds. The package execution failed. The step failed.
Joanne Douglass commented
Same issue running on 2016 (13.0.4457.0). We do have a few packages that start every minute or every few minutes, which is something I've seen people elsewhere mentioning as a potential cause. However, things were running successfully for a while without this problem occurring, and it has started happening when nothing no new jobs have been created and no upgrades or patches applied to SQL Server for several weeks.
I have SSISDB in a Basic Availability Group, but that has been the case for several months. Other than that, there's nothing unusual about the instance.
Same issue - I see this behavior in combination with enabled resource governor only.
Same issue on SQL Server 2016 (13.0.4001.0). Got this exception afters putting the SSISDB in offline mode because a package failed to start and no logging was available. I've seen this multiple times now.
No queries are being executed, package fails to start and hangs infinitely. When setting the SSISDB in offline mode, and back in online mode it starts working again. (Or stopping the agent job) The package having the problem logs messages in the first step. 95% of the runs are ok and we have the logging. But the other 5% it hangs until we stop it. It seems to me, a connection is not closed, or there should be a timeout somewhere if it fails to write to the SSISDB.
: Failed to execute IS server package because of error 0x80131904
Description: Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded. Source: .Net SqlClient Data Provider Started: 8:55:39 Finished: 10:53:31 Elapsed: 7072.75 seconds. The package execution failed. The step failed.