SQLTeam.com | Weblogs | Forums

Dont send email if table is not populated


#1

I have a ssis package that basically gets data from a sql table (source) then emails the report as an xls attachment (destination). That part works ok, what i am trying to do is to not send emails if the report table is empty. so first i would check to see if the table i am getting my results from is empty if it is then dont proceed to the send email task. Also i would also like to email recipients only if they are assigned to a a particular department within my table. so i have a column named department in my report and persons are assigned to individual departments.


#2

There are two methods to do the same one of them is a using standard subscription method or you can create a custom code which fires the same query in your report. Please go this one here you will find the steps that involved to this in pictorial format. http://scn.sap.com/community/bi-platform/blog/2014/08/11/avoid-sending-email-if-report-is-empty


#3

i figured out, i just added a execute task that does a count then assign a variable, then within my result set of the execute task set the resultname to 0 and pass my new variable. Then add a precedent constraint expression @variable >= 1, so whenever my table has values greater than or equal to 1 it will move onto the email task. if counts = 0 now emails will be sent