When running SSIS components through SQL Server Agent jobs sometimes an error may appear and when viewing the job history you’ll see the helpful “To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report”. If you attempt this you may get an “Out Of Memory” exception due to the sheer amount of logging your SSIS component is producing.
So how do you troubleshoot?
There’s a way of querying the report through an SQL query and you can filter down the noise accordingly to something similar to the below:
SELECT TOP 15 *, event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_type
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
AND event_name NOT LIKE '%Validate%'
/* Put in whatever WHERE predicates you might like*/
WHERE event_name = 'OnError'
ORDER BY message_time DESC
Kudos to the following link that refreshed my memory on this:-