An issue had come up recently that the customer reports that sent three times a day was not delivered. After some investigation, I have realised one of the email address in the database was wrong hence causing the process failed half way through. Hence half of the clients missed out on the report.
Surely, it was a user typo, however, the process should be smart enough to report the error and carry on with the rest of the clients having good email addresses. Therefore, two improvements are put forward to be implemented in the SSIS process:
- Report the error by email
- Continue sending emails to other unaffected clients
After some research on Google, I have found two links that were very useful to help me solve this problem.
Report the error by email
This is in fact very simple:
- Select the Send Mail Task, then click the Event Handler tab;
- Select the OnError event, then click the link in the middle of the screen to create the event;
- Add a Send Mail Task to the OnError event to send email notification when an error occur.
- That’s all, do the next step if you do not want the error to interrupt the looping.
Web reference: http://stackoverflow.com/questions/5433132/handling-failure-of-the-send-mail-task-in-ssis
Continue sending emails to other unaffected clients
While the first step was very simple, the second step is even simpler:
- To Prevent the error from going further up the chain, open up the System Variables (i.e. SSIS menu > Variables, then select Show System Variables) from within the OnError event handler of the Send Mail Task;
- Find and change the Propogate property’s value to False;
- That’s all you need to do, do the same steps to all the tasks within the FOREACH loop container to prevent an interruption to the loop.
1 comment on “SSIS: Handling failure in Send Mail Task within a FOREACH loop container”