I have deployed my SSIS solution to SQL Server 2014. The package is run manually from Management Studio. Randomly, the package shows status running in execution report, but when I connect to to Integration Service using Management Studio it doesn't show any package running. The package just hangs without any error, nor any step is started or completed. How can I troubleshoot this? What could be causing this error?
1
votes
1 Answers
0
votes
Just for the records, I found that the problem rely in a database timeout caused by having too much logs in SSISDB tables.
The SSIS Execution Process could not write to the IS catalog: MAMSQLT02:SSISDB Error details: Unable to add new messages to the table of operation messages.:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.; at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ISServerHelper.AppendEventMessage(SqlCommand cmd, Int64 operationId, Int32 message_type, DateTimeOffset message_time, Int16 message_source, String message_text, Nullable1 extendedInfoId, String packageName, String packageLocationType, String packagePathFull, String eventName, String messageSource, String messageSrcId, String subComponentName, String packagePath, String executionPath, UInt32 threadId, Nullable1 messageCode, Int64& eventMessageId) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.BasicEventInfo.WriteInternal(SqlCommand sqlCommand) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.Loggable.Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ILoggable.Write(SqlConnection conn)
Truncating the tables using SP [internal].[cleanup_server_retention_window_truncateall] solved the problem
