Sep 25, 2009

Send Email when No Rows are transferred in Data Flow Task of SSIS - SQL 2008

There might be some situations when user must be notified that NO RECORD was transferred to destination while executing SSIS Package. There is no one way of doing this, however, I find the following as the most simple and less complicated way. The steps are:

1. Declare a variable at project Scope level in SSIS.



2. Open the Data Flow task where the 0 row count needs to be validated. Add Row Count task between Source and Destination, and assign row count result to a variable that was declared above. Look at the following two images:

 



3. Go back to control Flow Tab, Just below the Data Flow task, that was modified above, add a Precedence Constraint and join it to Send Mail Task, add another Precedence Constraint and Join it to the task that should be executed next. At this point, under the same Data Flow Task, there will be two Precedence Constraints with Green Color (Success). Next, condition needs to be added to these Precedence Constraints, so that in case there is no record an email should be sent to the user.

4. Remember that already a variable has been set above with Row Count, same variable will be used here to decide whether there was any row returned or not. Configure the Precedence Constraint just similar to what is shown in figure below. Doing this, it will make sure that email will be sent only if RowCount is 0.



5. Configure second Precedent Constraint, that was added later so that step next to is executed if RowCount is greater than 0. Similar the screen below:




6. Once these steps are completed, you will notice 'fx' icon next to each of the Precedent Constraint.
7. Next, Set the Precedent Constraint between Email Task and the task that should be executed when more than 0 records are transformed. This step should be ignored, if SSIS has not to proceed further if 0 rows are returned.

After completing all the steps, the Control Flow Task will appear like:






22 comments:

  1. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post. ActiveCampaign Pros

    ReplyDelete
  2. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
    email search tool

    ReplyDelete
  3. Thanks for the blog post buddy! Keep them coming... find contact info

    ReplyDelete
  4. Excellent post. I was reviewing this blog continuously, and I am impressed! Extremely helpful information especially this page. Thank you and good luck. Email Login Tips

    ReplyDelete
  5. This is a great inspiring article.I am pretty much pleased with your good work.
    hotmail

    ReplyDelete
  6. great description the way you write the details are amazing thanks for sharing this nice article hotmail.com

    ReplyDelete
  7. OMG i am confused because i thought it can't be done you open up my eyes surely going to try this trick thank you.

    ReplyDelete
  8. Thanks for the details yahoo mail I am sure many people will find it useful.

    ReplyDelete
  9. You delivered such an impressive piece to read, giving every subject enlightenment for us to gain information. Thanks for sharing such information with us due to which my several concepts have been cleared. crie uma conta no Hotmail email gratuito

    ReplyDelete
  10. Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work. hotmail entrar

    ReplyDelete

Optimizing Indexes with Execution Plans

Contact Me

Name

Email *

Message *