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: 246695 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 🙂

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.

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!