August 20, 2009 at 12:05 pm
I have a package that will load a data from source to dest, and it will write an error ouput rows in a flat file, I have condition that maximum error should be 50. when ever there is an error upto 50 on a flat file it should send me an email, I am able to do this task, but if there are no errors, i am still getting an email message. how to avoid an email message when i doesnt get any error on flat file?
August 20, 2009 at 12:12 pm
I assume this is the package from your other thread?
http://www.sqlservercentral.com/Forums/Topic761855-148-11.aspx#bm772890
You can accomplish this by setting the precedence constraint between your data flow task and the e-mail task to use an expression. The expression should check the value of the # of errors from your data flow. This means that you'll need to get the error count (# of rows in your error handling portion of your data flow) into a variable. The variable can then be used in the expression inside the precedence constraint.
August 20, 2009 at 12:17 pm
Ya i did that, but I dont know where i screwed up, i am getiing an email evertime the package executes.
August 20, 2009 at 12:23 pm
http://support.microsoft.com/kb/906547
I was using above link while confugring email task
August 20, 2009 at 12:42 pm
Yep, that's what I'm talking about. So it does not work for you.
What does your expression inside your precedence constraint look like?
August 20, 2009 at 12:57 pm
Here I have attached scren shot, there are 3 screnshots. I have created 3 variables, errorrowcount,ident and maxcount, and put maxcoun have value to 50,.let me know if you have any question.
August 20, 2009 at 1:02 pm
Have you set a breakpoint on your mail task and looked at the run time value of your errorcount variable? Where is the variable getting populated?
August 20, 2009 at 1:20 pm
Sorry I have no idea how to set up breakpoint in mail task and I am relatively new to ssis so i am just doing what i am understanding from microsoft documents.
August 20, 2009 at 1:28 pm
Well, we need to determine if your error count variable contains the value you expect it to. I'm guessing that it does not because your expression looks OK and it is not working as you expect. That leaves the variable to look at.
1. Left click once on your mail task to set the focus on that task.
2. Expand the Debug menu in BIDS and select 'Toggle Breakpoint'. (you can just hit F9 instead of using the menu, but it helps to know where it is).
3. Run the package. SSIS will halt execution when it reaches the breakpoint.
4. When SSIS is at the breakpoint, Go back to the Debug menu and select Windows>>Watch>>Watch 1. This will bring up the Watch window.
5. Under the Name column in the Watch window, type in your variable name [User::variablename]. The watch window will then display the value of that variable in the Value column. If it does not, make sure you typed in the variable name correctly, including case. You can also go to the Package Explorer and drag the variable name into the watch window.
August 20, 2009 at 1:43 pm
Yes i did as per your recomendation, I have attached the file, so what should i do next?
August 20, 2009 at 2:05 pm
So your error count variable is zero. Based on your expression, the email will be sent. Your expression is errorcount = maxcount?
August 20, 2009 at 2:23 pm
thanks seems to be working now.
August 20, 2009 at 2:25 pm
August 20, 2009 at 2:29 pm
I have a short question what if I get 20 errors, in this senario also i need to get an email. does it work with that expression since i need to get an email when ever an error occurs.
August 20, 2009 at 2:30 pm
ya i change errorcount>=maxcount on expression and it worked.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply