I'm not a fan of this SSIS catalog reports to identify errors for job failures so I had to find a way to modify a report I already had with the error message from the MSDB database. I added the SSIS Catalog Error Message by using this approach:
1) Find the SSIS Package path in the MSDB database on the sysjobsteps, had to use SUBSTRING/CHARINDEX to get the actual package name from the command column.
2) Find the agent job execution date/time from the sysjobhistory table.
3) Find the SSIS Package path in the SSIS database on the catalog.executions view by concatenating '\SSISDB\' + folder_name + '\' + project_name + '\' + e.package_name
4) Find the package execution start_time on catalog.executions
5) Add ROW_NUMBER to each agent job step exec/ssis package exec order by the startdate
6) Join the MSDB with the SSIS on packagename and row_number and status = 4 (failure) to match up MSDB/SSIS execution and get the execution ID.
7) Then find the error message for the failure using the SSISDB.catalog.event_messages, filtering on operation_id with the execution ID and event_name = 'OnError'
I'd be happy to share the code, I'm sure there's probably a better way to do this but it works find for me for what I need. Message me if interested.