Send Mail if table is empty

[MikeAuz1979]

Hi,

I have a data flow task that truncates and appends to a table. The thing is that if this table has any results then these need to be investigated so after the data flow task I'd like a task that checks to see if the row count of the table is > 0 and if so sends an email.

Also I have a few packages where I'm emailing myself onError but it's just a hardcoded email address in each package is there a way of setting up a single global email address so if it changes in the future then it will only be changed in one place?

Thanks for any Help
Mike

 

[drtree]

Mike,

Check out this link to see how to set a global e-mail for your SSIS packages -

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71908.

As far as sending out an e-mail conditionally, I've only done this if a task fails, so I can't be much help to you. My suggestion would be that you'd have to use an Execute Sql task, store the number of rows gotten from the task in a variable and only execute your Send Mail task if the variable is greater than 0.

HTH,

Doc Tree

 

[mrdenny]

You could also use a basic IF EXISTS query within a SQL task and send an email if there are any records in the table.

IF EXISTS (SELECT * FROM TABLE)
    exec sp_send_dbmail...

Setup an exchange distribution list for the address to send to. Then if the person/people that need to get the email change, you can simply change the members of the distribution list.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog