How to add a table data as a message in ssis send Mail Task

Question

Post reply

How to add a table data as a message in ssis send Mail Task

asita

SSCarpal Tunnel

Points: 4144
More actions
December 15, 2009 at 8:40 am

#156505

Hello All,
Please go through the below.........
in my ssis package it loads data from some Database tables(it is a different software) to sql server Table,
in the ssis package as a last step i have to send EMAIL when ever a table has data (if there is no rows then i dont need to send any mail) , if there is any rows in that table then i have to send a mail
how can i achive this
i have knowledge on Email task,
so please help me logic (and steps that i need to take care of) , especially how to include the table data in a mail please....
please
Thanks in advance masters
asita

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

asita SSCarpal Tunnel Points: 4144 More actions · Answer 1

Could you assist me

can we send the table data as a message is it possible?

please...............

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

asita (12/15/2009)
Could you assist me
can we send the table data as a message is it possible?
please...............

Yes it is possible. Use the Code below

DECLARE @xml NVARCHAR(MAX), @body NVARCHAR(MAX) , @SUBJ VARCHAR(8000)

CREATE TABLE #Temp ([NAME] [varchar](128) ,

[STATUS] [varchar](500) ,

[int] NULL )

INSERT INTO #Temp

SELECT 'AAA','STATUS 1' ,1

UNION ALL

SELECT 'BBB','STATUS 2' ,2

UNION ALL

SELECT 'CCC','STATUS 3' ,3

IF EXISTS(SELECT TOP 1 * FROM #Temp)

BEGIN

SET @xml =CAST(( SELECT [NAME] AS 'td','',[STATUS] AS 'td','', AS 'td'

FROM #Temp

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H3>TEST REPORT</H3><body >

<table border = 1><tr><th>NAME</th><th>STATUS</th><th>CODE</th></tr>'

SET @body = @body + @xml +'</table></body></html>'

SET @SUBJ = @@SERVERNAME + ' REPORT'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL ALERT PROFILE',

@body = @body,

@body_format ='HTML',

@recipients = 'you@yourdomain.com',

@subject = @SUBJ;

END

DROP TABLE #Temp

You got to change it as per you table, columns and also the Database Mail profile and email details.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 3

[font="Comic Sans MS"]

There is an excellent article on more generic topic "Sending scheduled SQL query results via HTML e-mail using SSIS" by Paul Clancy - you may wish to have a look..

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

How to add a table data as a message in ssis send Mail Task

Cookies on SQLServerCentral