Your configuration means you are getting an alert sent to the operator configured for the job.

From your description what you really want is an email that summarises the failure.

I have a SSIS package that does something similar which loops over all our servers, and sends a summary email of all job failures that have not subsequently been successful looking back over X hours.

I can't share the package as it has some sensitive data in it but I have included some of the code below to help you along:

Create this procedure on your sever you want to report from in a suitable database:

CREATE PROCEDURE usp_report_failed_jobs @CheckOverLastXHours int AS

BEGIN

-- Build list of all succeeded jobs

WITH CTE_MostRecentSuccess AS (

select j.name AS JobName,

MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date]

FROM msdb..sysjobs j

JOIN msdb..sysjobhistory h on j.job_id = h.job_id

WHERE j.enabled = 1

AND h.run_status = 1

AND h.step_name = '(Job outcome)'

AND h.run_time >= 1000

AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())

GROUP BY j.name),

---- Build list of all failed job steps

CTE_Currentfailures AS (

select j.name AS JobName,

MIN(CASE WHEN step_name = '(Job outcome)' THEN [message] END) AS [Step Failure Message],

MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date],

ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY run_date DESC, run_time DESC) RankNo

FROM msdb..sysjobs j

JOIN msdb..sysjobhistory h on j.job_id = h.job_id

WHERE j.enabled = 1

AND h.run_status <> 1

AND h.step_name = '(Job outcome)'

AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())

GROUP BY j.name, h.run_date, h.run_time)

-- Report a List of jobs which latest status is a failure in the last 8 hours.

SELECTCAST(@@ServerName AS NVARCHAR(100)) AS Server,

CAST(f.JobName AS NVARCHAR(1000)) AS [Job Name],

CAST(f.[Step Failure Message] AS NVARCHAR(4000)) AS [Step Failure Message] ,

f.[Scheduled Run Date]

FROM CTE_Currentfailures f

LEFT JOIN CTE_MostRecentSuccess s on f.JobName = s.JobName

WHERE f.RankNo = 1

AND f.[Scheduled Run Date] > ISNULL(s.[Scheduled Run Date],'1980-01-01')

AND f.[Scheduled Run Date] > DATEADD(hh,-@CheckOverLastXHours,getdate())

END

Then call the procedure and send the results via an email with HTML format this code gives you a basis for the email sending:

SET NOCOUNT ON

--Declare variables for use througout the code.

DECLARE @EmailRecipient VARCHAR(1000)

DECLARE @emailSub NVARCHAR(256)

DECLARE @ProfileName VARCHAR(1000)

DECLARE @jobHTML VARCHAR(MAX)

DECLARE @status INT

DECLARE @LastXHours INT

DECLARE @Environment NVARCHAR(100)

DECLARE @ServerAdminGroup NVARCHAR(100)

-- SET Variable Values

SET @EmailRecipient = 'test@yourcompanycom'

SET @LastXHours = 1

SET @Environment = 'PRODUCTION'

/**********************************************************************/

/*!!!!!!!!!!!!!!!! DO NOT MODIFY BELOW THIS LINE !!!!!!!!!!!!!!!!!!!!!*/

/**********************************************************************/

SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile

-- Remove Failed Jobs from the list if they have an exception

DELETE FROM tmp_sql_failedjobs

WHERE EXISTS (

SELECT *

FROM sql_failedjobs_exceptions E

WHERE E.Server = tmp_sql_failedjobs.Server

AND E.JobName = tmp_sql_failedjobs.JobName

)

-- if all previous top X excecutions of the job are over the threshold

-- start formatting the email.

SET @status =CASE WHEN (SELECT COUNT(1) FROM tmp_sql_failedjobs) > 0 THEN 1

ELSE 2

END

SET @emailSub = CASEWHEN @status = 1 AND @Environment = 'PRODUCTION' THEN '!!! PRODUCTION Job Failures Detected !!!'

WHEN @status = 1 AND @Environment <> 'PRODUCTION' THEN '!!! NON PRODUCTION Job Failures Detected !!!'

END

--Set the html text for the jobs and or package failures.

SET @JobHTML =

N'<H3 style="color:red; font-family:verdana">Job failures in the last '

+ CONVERT(VARCHAR(10),@LastXHours) + ' hours have been detected which have no subsequent successful completion, see details below' +

'</H3>' +

N'<p align="left" style="font-family:verdana; font-size:8pt">

' +

N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +

N'<tr style="color:42426F; font-weight:bold"><th>Admin Group</th><th>Environment</th><th>Server</th><th>Job Name</th><th>Step failure Message</th><th>Execution Date</th></tr>' +

CAST((

SELECT

td = ServerAdminGroup, '',

td = EnvType, '',

td = Server, '',

td = JobName, '',

td = StepFailureMessage, '',

td = ScheduledRunDate

FROM tmp_sql_failedjobs

ORDER BY Server, JobName

FOR XML PATH('tr'), TYPE

) AS VARCHAR(MAX) ) +

N'</table>'

IF EXISTS (SELECT TOP 1 1 FROM tmp_sql_logprogress WHERE JobCollectionSucceeded = 0 OR ConnectionAvailable = 0)

BEGIN

SELECT @jobHTML = @jobHTML + N'<p style="color:blue; font-family:verdana">The following servers encountered an error when performing failed job collection ''' +

N'<p align="left" style="font-family:verdana; font-size:8pt">

' +

N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +

N'<tr style="color:42426F; font-weight:bold"><th>Type</th><th>Category</th><th>Server</th><th>Connect Success</th><th>Collect Success</th><th>ErrorDetails</th></tr>' +

CAST((

SELECT

td = ServerAdminGroup, '',

td = EnvType, '',

td = Server, '',

td = CASE WHEN ConnectionAvailable = 1 THEN 'Y' ELSE 'N' END, '',

td = CASE WHEN JobCollectionSucceeded = 1 THEN 'Y' ELSE 'N' END, '',

td = ISNULL(ErrorDetails,'N/A'),''

FROM tmp_sql_logprogress

WHERE ConnectionAvailable = 0 OR JobCollectionSucceeded = 0

ORDER BY ServerAdminGroup, EnvType, Server

FOR XML PATH('tr'), TYPE

) AS VARCHAR(MAX) ) +

N'</table>'

END

IF @status = 1

BEGIN

--Send the status report email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName,

@recipients=@EmailRecipient,

@body=@jobHTML,

@body_format = 'HTML',

@subject =@emailSub

END

SELECT @JobHTML

This is not a complete solution, but gives you a start.