How to get detailed error message of last step when job failed.

EmailLastFailedStep_SendErrorMessage.sql

USE [msdb]
GO
CREATE PROC [dbo].[EmailLastFailedStep_SendErrorMessage]
    @JobName nvarchar(128)
   ,@recipients varchar(4000)
   ,@profile_name nvarchar(128)
   ,@debug bit = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
	declare @body varchar(max)
		  , @ServerName varchar(100)
		  , @table_title varchar(200)
		  , @db_email_profile varchar(40)
	IF ISNULL(@JobName,'')=''
		RAISERROR ('Please provide JobName.', 16, 1);
	IF ISNULL(@recipients,'')=''
		RAISERROR ('Please provide Recipients.', 16, 1);
	IF ISNULL(@profile_name,'')=''
		RAISERROR ('Please provide profile_name.', 16, 1);

	set @ServerName = CONVERT(sysname, SERVERPROPERTY('servername'));
    set @table_title = '['+@ServerName+']'+'SQL Server Job('+@JobName+') - Failure Report'

	IF not exists (select 1
		  from [msdb].[dbo].[sysmail_profile] p
		 where name = @profile_name) begin		
		SELECT @db_email_profile=p.name
         FROM [msdb].[dbo].[sysmail_principalprofile] pp
            JOIN [msdb].[dbo].[sysmail_profile] p
               ON pp.profile_id = p.profile_id
       WHERE pp.[is_default] = 1
	end else begin
		SET @db_email_profile=@profile_name
	end

	set @body = cast( (
	select td = 'class="' + tr_class + '"><td>' + servername + '</td><td>' + job_name + 
				'</td><td>' + convert(varchar(20),start_execution_date,120) +
				'</td><td>' + convert(varchar(20),stop_execution_date,120) + 
				'</td><td>' + convert(varchar(10),run_duration) +
				'</td><td>' + run_status + '</td><td>' + convert(varchar(5),last_step_id) +
				'</td><td>' + convert(varchar(max),message) + '</td><td>' + convert(varchar(max),job_description)
	  from (
				select d.servername
							 , d.job_name, d.start_execution_date, d.stop_execution_date, d.run_duration
							 , d.run_status
							 , d.last_step_id, isnull(m.SSISMessage,d.message) message
							 , d.job_description
							 , d.tr_class, d.PackageName
				  from (
							select @ServerName servername
								 , j.name job_name, start_execution_date
								 , convert(datetime,left(jh.run_date,4)+'-'+substring(jh.run_date,5,2)+'-'+right(jh.run_date,2)+' '+
								   left(jh.run_time,2)+':'+substring(jh.run_time,3,2)+':'+right(jh.run_time,2),120) stop_execution_date
								 , run_duration
								 , case run_status when 0 then 'Failed' when 1 then 'Succeeded'
								   when 2 then 'Retry' when 3 then 'Canceled' else 'N/A' end run_status
								 , last_step_id, isnull(message,'') message
								 , isnull(description,'') job_description
								 , case when row_number() over(order by j.name)%2 = 1 then 'odd' else 'even' end  tr_class
								 , case when s.command LIKE '%.dtsx%' then RIGHT(LEFT(s.command, Charindex('.dtsx', s.command)-1),
								   Charindex('\', Reverse(LEFT(s.command, Charindex('.dtsx', s.command)-1)))-1) + '.dtsx' end PackageName
								, s.subsystem
							  from msdb.dbo.sysjobs j
							 cross apply (
								   select top 1 
										  start_execution_date, last_executed_step_id, last_executed_step_date
										--, stop_execution_date, job_history_id
									 from msdb.dbo.sysjobactivity ja
									where ja.job_id = j.job_id
									  and ja.start_execution_date >= dateadd(hh,-24,getdate())
									  and ja.start_execution_date <= getdate()
									order by run_requested_date desc
									) ja
							 cross apply (
								   select top 1
										  jh.step_id, jh.run_status, jh.run_duration
										, jh.step_id last_step_id, jh.run_status last_step_run_status
										, jh.message, convert(varchar(8),jh.run_date) run_date
										, convert(varchar(6),jh.run_time) run_time
									 from msdb.dbo.sysjobhistory jh					  
									where jh.job_id = j.job_id 
									  and jh.step_id <> 0
									  and jh.run_status = 0 -- 0: Failed, 3: Canceled
									  and jh.step_name <> 'Send Email'
									order by jh.instance_id desc
								   ) jh
						   join msdb.dbo.Sysjobsteps s (NOLOCK)
							  on s.Job_id = j.job_id
							and s.Step_id = jh.last_step_id
							 where j.name = @JobName
						  ) as d
					  outer apply (
									select '<table cellpadding="2" cellspacing="2" border="1">' +
												'<tr><th>message time</th><th>Messages</th><th>message_source_name</th><th>subcomponent_namee</th></tr>' +
												replace(replace( replace( cast( (
												select td = convert(varchar(20),em.message_time,112) + '</td><td>' + isnull(em.message,'') + 
															'</td><td>' +isnull(em.message_source_name,'') +
															'</td><td>' + isnull(em.subcomponent_name,'')
												 from (
														select em.[event_message_id], em.message_source_name, em.subcomponent_name, em.message_time, em.message
														  from (select top 1 e.execution_id operation_id
																	  from ssisdb.ssisdb.[internal].[executions] e with(nolock)
																	where e.package_name=d.PackageName
																	and d.subsystem='SSIS'
																	order by e.execution_id desc
																	) o
															cross apply(
																	select em.[event_message_id], em.message_source_name, em.subcomponent_name, om.message_time, om.message
																	   from ssisdb.ssisdb.internal.event_messages em with(nolock)
																	   join ssisdb.ssisdb.[internal].[operation_messages] om with(nolock)
																		  on om.[operation_message_id] = em.[event_message_id]
																	 where o.operation_id=em.operation_id
																	 --and em.package_name=d.PackageName
																	 and em.Event_name = 'OnError'
																) em
														) em
											order by em.message_time, em.[event_message_id]
											for xml path( 'tr' ) ) as varchar(max) )
												, '&lt;', '<' ), '&gt;', '>' ), '><td>class="', ' class="') +
												'</table>' SSISMessage
							) m
		  ) as d
	for xml path( 'tr' ) ) as varchar(max) )

	IF @body IS NULL
		RAISERROR ('No error message found.', 16, 1);

	set @body = '<style> ' +
				'TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} ' +
				'TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;} ' +
				'TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;} ' +
				'.odd  { background-color:#ffffff; } ' +
				'.even { background-color:#dddddd; } ' +
				'</style>'
			  + '<table cellpadding="2" cellspacing="2" border="1">'
			  + '<tr><th>Server Name</th><th>Job Name</th><th>Start Exec Date</th><th>Stop Exec Date</th><th>Run Duration</th><th>Run Status</th><th>Last Step</th><th>Error Message</th><th>Job Description</th></tr>'
			  + replace(replace( replace( @body, '&lt;', '<' ), '&gt;', '>' ), '><td>class="', ' class="')
			  + '</table>'

	IF @debug = 1
		print @body
	ELSE
		EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_email_profile,
		@recipients = @recipients,
		--@copy_recipients = '',
		@body = @body,
		@body_format = 'HTML',
		@importance = 'Normal', -- high,normal,low
		@subject = @table_title
	RETURN 0
END TRY
BEGIN CATCH
    SELECT  ERROR_LINE() AS [Error_Line],
            ERROR_MESSAGE() AS [Error_Message],
            ERROR_NUMBER() AS [Error_Number],
            ERROR_SEVERITY() AS [Error_Severity],
            ERROR_PROCEDURE() AS [Error_Procedure];
        throw;
	RETURN -1
END CATCH
go

1. Create EmailErrorDetail step and call [msdb].[dbo].[EmailLastFailedStep_SendErrorMessage]
2. Modify job step flow
   2.1 EmailErrorDetail step
   2.1.1 On Success - Quit the job reporting failure
   2.1.2 On Failure - Quit the job reporting failure
   2.2. Other steps that you want to send detail error message
   2.1.1 On Success - Go to the next step
   2.1.2 On Failure - Go to step: [N]EmailErrorDetail