Post reply

Send mail based on count from SQL Task

  • May 8, 2009 at 3:26 pm

    #384549

    I just need a little direction. I have some T-Sql code that I have to run and if the row count comes back greater than 0 I want to send off an email. Because of some rights issues in our environment I need to do this from SSIS although I realize it would be much easier to do it all through T-SQL and use dbmail. Just need some insight on the best and easiest way to accomplish this through SSIS. Thanks.

  • May 8, 2009 at 4:18 pm

    #991507

    EXEC MSDB.dbo.SP_SEND_DBMAIL

    @profile_name = 'Reporting Team'

    ,@recipients = 'a@microsoft.com; ,@copy_recipients =@CCList

    , @subject = N'Phone Volume'

    , @query_result_header = 0

    ,@body = @tableHTML

    ,@body_format = 'HTML'

    Use the above format and make sure you create the profile, if you dont have one and @body it can be anything. This is the easiest way

    Simon

  • May 9, 2009 at 1:40 am

    #991571

    - Create an int variable, scoped at package level, to hold the rowcount results.

    - Use an Execute SQL task to run the count query and assign the result to rowcount

    - Add a Send Mail task after the Execute SQL task, with a precedence constraint controlled by the value of rowcount

    job done!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 11, 2009 at 3:26 pm

    #992503

    Thanks Phil. Based on your instructions I was able to get it to work. Thanks again.

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

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