Script not emailing from Maintenance Plan/SSIS

Question

I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL Statement Task'

MSSQL 2008 R2

USE msdb

DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailProfile VARCHAR(100) = 'SQL_Alert'
DECLARE @Email VARCHAR(100) = 'my email is here'
DECLARE @JobName VARCHAR(200) = 'job name is here'
DECLARE @Subject VARCHAR(200) = @JobName + ' Status: '

;WITH CTE
AS 
(
SELECT distinct top 1 sj.name AS [Agent Job Name],
CASE WHEN sjh.run_status=0 THEN 'Failed'
                 WHEN sjh.run_status=1 THEN 'Succeeded'
                 WHEN sjh.run_status=2 THEN 'Retry'
                 WHEN sjh.run_status=3 THEN 'Cancelled'
           ELSE 'Unknown'  
      END [Job Outcome],
sja.run_requested_date, 
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM    msdb.dbo.sysjobactivity sja
INNER   JOIN msdb.dbo.sysjobs sj
ON  sja.job_id = sj.job_id
INNER   JOIN msdb.dbo.sysjobhistory sjh
ON  sjh.job_id = sj.job_id
WHERE   sja.run_requested_date IS NOT NULL
AND sj.name = @JobName
AND sjh.step_id = 0
)

SELECT @EmailBody = 
'Agent Name: '
 + CHAR(9)
 + (SELECT [Agent Job Name] FROM CTE) 
 + CHAR(13)
 + 'Job Outcome: ' 
 + CHAR(9)
 + (SELECT [Job Outcome] FROM CTE)
 + CHAR (13)
 + 'Ran At: ' 
 + CHAR(9)
 + CONVERT(VARCHAR(100), (SELECT run_requested_date FROM CTE), 25)
 + CHAR (13)
 + 'Duration: ' 
 + CHAR(9)
 + (SELECT [Duration] FROM CTE)

IF (@EmailBody IS NOT NULL)
BEGIN
SET @Subject = @Subject +
CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
     WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
     WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
     WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
     ELSE 'Unknown'
     END;

EXEC sp_send_dbmail 
@profile_name = @EmailProfile,
@recipients = @Email,
@subject = @Subject,
@Body = @EmailBody
END

Results from history

Executing query "USE msdb DECLARE @EmailBody VARCHAR(MAX) DECLA...".: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 11:38:55 AM Finished: 11:38:58 AM Elapsed: 2.995 seconds. The package executed successfully. The step succeeded.

Answer 1

Currently, your code will not send any email if any fields used to create the subject or body are NULL.

I'd add a case to deal with that such as:

IF (@EmailBody IS NOT NULL)
BEGIN
    SET @Subject = @Subject +
    CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
         WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
         WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
         WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
         ELSE 'Unknown'
         END;

    EXEC sp_send_dbmail 
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END
ELSE /* catch any issues with NULL for @EmailBody */
BEGIN
    SET @EmailBody = 'nothing to report';
    SET @Subject = 'alert!';
    EXEC sp_send_dbmail 
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END