email query result ssis

Hello,
There is a table that I want to select and send it to email recipient n outlook….how to do that in SSIS?
SELECT [Pl]
      ,[Brnds]
      ,[fiscalMonth]
      ,[Dollars]
  FROM [dummyDB].[dbo].[2013_oldDataTbl]
RayneAsked:
Who is Participating?
 
Dheeraj BabooTeam LeadCommented:
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
   N'<head>' +
    N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
   N'<h2><font color="#0000ff" size="4">Test Report</font></h2>' +  
   N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
   
         
    N'<table border="1">' +
    N'<tr>'+
            N'<th>Pl</th>'+
            N'<th>Brands</th>' +
            N'<th>Fiscal Month</th>'+
            N'<th>Dollars</th>
    </tr>' +
      
      CAST ( ( SELECT td = Pl,       '',
                    td = Brnds, '',
                    td = fiscalMonth, '',
                    td = Dollars, ''
              FROM [dummyDB].[dbo].[2013_oldDataTbl]
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail @recipients='toEmailId@abc.com',
    @profile_name = 'SQl',
    @subject = 'Test Report',
    @body = @tableHTML,
    @body_format = 'HTML' ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.