enviar mail con integration services con cuerpo de una consulta sql

[Libras]

Prueba con esto:

Código SQL:

CREATE TABLE #DriveSpaceLeft (Drive VARCHAR(10),
                              [MB Free] BIGINT )
 
INSERT #DriveSpaceLeft (Drive, [MB Free])
   EXEC master.dbo.xp_fixeddrives;
 
CREATE TABLE DrivesWithIssue (Drive VARCHAR(10),
                              [MB Free] BIGINT )
 
INSERT INTO DrivesWithIssue 
  SELECT Drive, [MB Free] FROM #DriveSpaceLeft
  WHERE [MB Free] < 1000
 
DROP TABLE #DriveSpaceLeft
 
DECLARE @cnt INT  
SELECT @cnt=COUNT(1) FROM DrivesWithIssue
IF (@cnt > 0)
BEGIN
 
    DECLARE @strsubject VARCHAR(100)
    SELECT @strsubject='Check drive space on ' + @@SERVERNAME 
 
    DECLARE @tableHTML  nvarchar(MAX);
    SET @tableHTML =
        N'<H1>Drives with less that 1GB Free  - ' + @@SERVERNAME + '</H1>' +
        N'<table border="1">' +
        N'<tr><th>Drive</th>' +
        N'<th>MB Free</th></tr>' +
        CAST ( ( SELECT td = [Drive], '',
                        td = [MB Free]
                  FROM DrivesWithIssue
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +      
        N'</table>' +        
        N'<br> Please review the database space and the log files'
        ;
 
    EXEC msdb.dbo.sp_send_dbmail
    --@from_address='[email protected]',
    @recipients ='mail',
    @subject = @strsubject,
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @profile_name='profile'
END
 
DROP TABLE DrivesWithIssue
 

este formato envia un mail cuando el espacio en disco es menor de 1 GB.