CODEWITHSUNDEEP
ssis
Lrn
Lrn

Reputation: 327

Send Mail Task based on output from Execute SQL Task

How to send an email based on the ouput from the execute SQL Task in SSIS? If the query returns any results I would like to send an email if not the email should not be sent.

Upvotes: 2

Views: 9298

Answers (2)

Pondlife
Pondlife

Reputation: 16240

Do you want to send the results of the query in the mail or not?

Either way, you can do this:

  1. Capture the number of rows returned by your query in a variable
  2. Use an expression in your precedence constraint to send the email only if the rowcount was greater than zero

If you want the results of the query in the email, then the simplest thing is probably to write them to a flat file and then send the file as an attachment.

Alternatively, do the whole thing in a stored procedure using sp_send_dbmail and just call the procedure from your package.

Upvotes: 6

thursdaysgeek
thursdaysgeek

Reputation: 7936

It's probably a bit clunky, but you could send the results of your Execute SQL task to a variable, then use a For Loop container based on that variable. Put the Send Mail task within that For Loop container. The challange in that is only running the Send Mail once, no matter if the count is 1 or more. If your count was saved as a bool, perhaps that would work?

You could put another step in between, put in a Script Task that takes the count and returns a 0 or 1, and then use the For Loop with the Send Mail task within.

Like I said, clunky.

Upvotes: 0

Related Questions