SSIS - If Condition is Met, Send Email

Question

SSIS - If Condition is Met, Send Email

EjSQLme

SSCrazy

Points: 2172
More actions
November 3, 2010 at 1:23 pm

#240724

Hi,
How can I use SSIS to send an email is a condition is met?
For example, if I do a count and if the count is greater than 10, then send email.
I tried doing Data Flow Task then in the OLE DB Source I put in my SQL query (select count(*) as TotalCount from TableName where tableid >'0';), so my output will be the value of TotalCount, from there, I added the Conditional Split task, and got stuck. I have my Send Mail task in Control Flow section, but not sure how I would connect the Conditional Split task to the Send Mail Task if the output is greater than 10. Let me know what I'm doing wrong or if there is a different way of doing this.
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

You can't send the mail from the data flow. Just populate the variable and then use an expression in the precedence constraint to evaluate the variable and envoke the send mail task when the variable meets your condition.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

Use an Execute SQL task in your Control Flow to get the count and assign it to a variable - then use a precedence constraint to direct flow to your e-mail task, based on the contents of the variable.

-- Sorry John - I noticed that I almost quoted you verbatim when I looked back at what I'd written 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Phil Parkin (11/3/2010)
-- Sorry John - I noticed that I almost quoted you verbatim when I looked back at what I'd written 🙂

That would be a good compliment to me if you did.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

EjSQLme SSCrazy Points: 2172 More actions · Answer 4

EjSQLme

SSCrazy

Points: 2172

November 4, 2010 at 6:27 am

#1246480

Thank you guys..

EjSQLme SSCrazy Points: 2172 More actions · Answer 5

Is it possible to pass the output value to the Email Task body? If so, how?

John Rowan SSC Guru Points: 56440 More actions · Answer 6

You mean include the Row Count inside the email message....yes. You'll need to use the expression builder to build out the message body property (MessageSource, I think) and include the row count variable. Here's an example of how that type of expression would look:

"This is my variable value: " + (DT_WSTR, 10) @[User::Counter]

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

EjSQLme SSCrazy Points: 2172 More actions · Answer 7

EjSQLme

SSCrazy

Points: 2172

November 8, 2010 at 2:25 pm

#1247910

Got it... thank you!