Post reply

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

  • 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

  • December 15, 2009 at 9:27 am

    #1092594

    Could you assist me

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

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

  • December 15, 2009 at 9:51 am

    #1092609

    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

  • December 15, 2009 at 9:59 am

    #1092617

    [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]

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

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