SQL query to get the job error message from SSISDB

In SQL Server 2012 Microsoft did lot of enhancements in SSIS, especially by introducing the “Project Deployment Model”.  After this so many things got better in SSIS.  While there are so many good things about this change one thing started to irritate me and that is the error in the job history.  With this change we stopped getting the exact error messages in job history.  Instead, when we open the job history to check the reason for failure we see this message:
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report.
To get the exact job failure reason we need to go the Integration Services Catalog, right click on the Project name >> Reports >> Standard Reports >> All Executions.  These reports are very nice and detailed but every time going to these reports and trying to find the error is very irritating.  Sometimes too much information is not that good.  I have seen people making mistakes in reading these execution reports.  Most of the times we are interested in error messages only and not in other execution events.  Worst part is we can’t even copy the error message to quickly Google the error for solutions.  So I thought of doing something about it.  Every time I’m not going to open execution reports to see the error.
--------------------------------------------------------------------------------------------------------
DECLARE @DATE DATE = GETDATE() - 7 -- This is to restrict the data for last 7 days, used in ON condition
SELECT O.Operation_Id -- Not much of use
,E.Folder_Name AS Project_Name
,E.Project_name AS SSIS_Project_Name
,EM.Package_Name
,CONVERT(DATETIME, O.start_time) AS Start_Time
,CONVERT(DATETIME, O.end_time) AS End_Time
,OM.message as [Error_Message]
,EM.Event_Name
,EM.Message_Source_Name AS Component_Name
,EM.Subcomponent_Name AS Sub_Component_Name
,E.Environment_Name
,CASE E.Use32BitRunTime
WHEN 1
THEN 'Yes'
ELSE 'NO'
END Use32BitRunTime
,EM.Package_Path
,E.Executed_as_name AS Executed_By

FROM [SSISDB].[internal].[operations] AS O
INNER JOIN [SSISDB].[internal].[event_messages] AS EM
ON o.start_time >= @date -- Restrict data by date AND EM.operation_id = O.operation_id

INNER JOIN [SSISDB].[internal].[operation_messages] AS OM
ON EM.operation_id = OM.operation_id

INNER JOIN [SSISDB].[internal].[executions] AS E
ON OM.Operation_id = E.EXECUTION_ID

WHERE OM.Message_Type = 120 -- 120 means Error AND EM.event_name = 'OnError'
-- This is something i'm not sure right now but SSIS.Pipeline just adding duplicates so I'm removing it. AND ISNULL(EM.subcomponent_name, '') <> 'SSIS.Pipeline'
ORDER BY EM.operation_id DESC
--------------------------------------------------------------------------------------------------------
A quick SQL query to get the exact error would be nice.   And as I expected, all the package execution logs and error messages are stored in the SSISDB internal tables.   I created below sql script to query the SSISDB to get the exact error messages:
This was simple script to get the errors from SSISDB instead of going to Integration Services Catalogs execution reports.  Though this query is still work in progress, there could be scope for performance improvement or adding more information.  I’m still understanding the tables in SSISDB.  I’ll keep on posting the updates if any.

Mangal Pardeshi