0

Brief Summary:
A few days ago in production, SSIS suddenly stopped logging in the internal.operation_messages_scaleout, internal.event_messages_scaleout, internal.event_message_context_scaleout, and internal.executable_statistics tables of the SSISDB, but only for some of the package executions.

SSIS environment details:
SQL Server 2019 (15.0.4188.2)
SSIS 2019 Scale-Out (w/4 Worker Nodes)
LOGGING_LEVEL = 1 [Basic] (This is specifically set at execution time via the catalog.set_execution_parameter_value system stored proc.)

Background Details:
When trying to get details on a job failure yesterday, I went to the built in standard "All Executions" report that returned 8 failed executions for this specific package for that day. Clicking on the "Overview" link to bring up the overview report for one of the failures brought up the overview report showing the normal overview information, with the exception of the "Execution Overview" section, which only contained the message "No task information is available. One or more filters have been set, or one or more errors occurred during pre-execution. For information about the error messages, click View Messages to open the Messages report." enter image description here

Clicking on the "View Messages" link opens the messages report. The "All Messages" section of this report is blank, and the "Error Messages" section just shows the message "One or more errors have occurred in the tasks that are in the package. To see the OnError messages, click View Overview to open the Overview report, and select the tasks that are in the container."enter image description here

Clicking on the "View Overview" link takes me back to the same overview report which tells me to click the "View Messages" link bringing me back to view the messages report which tells me to click on the "View Overview" link bringing me back to the overview report which... You get the idea. Taking the execution_id for this execution, I queried the internal.operation_messages_scaleout, internal.event_messages_scaleout, internal.event_message_context_scaleout, and internal.executable_statistics tables in the SSISDB and all 4 of the queries came back empty.

So this is where it gets a little more interesting, the job that executes the package is setup to retry 3 times upon failure, every 5 minutes. The execution in question was the first retry after the original run failed. The Overview and Messages reports for the original run (5 min before) and subsequent retry attempts (5 and 10 min after) all contained the normal execution details / messages that I expected to see for the execution in question. As can be seen here... enter image description here

and here... enter image description here

If the lack of logging details was only happening for this particular job / package that would be one thing, but it is actually happening seemingly randomly across any job / package starting back on 6-2-2022 and gone from affecting ~10% of our ~2200 daily executions and has increased to over 50% as of 6-8-2022.

So does anyone have any thoughts of what is going on here? Ideas on what to try to help troubleshoot / resolve this?

1
  • Levi, did you get an answer to your post?
    – Jeff K
    Mar 24 at 10:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.