send email task on error in sql

Question

HI All,

I have a simple lookup transformation which finds matched and unmatched records, it creates table for matched and unmatched records. Now I want to send an email when the package finds any unmatched records and package should stop right there.

Thanks

Nick

Answer 1

Here are a few solutions:

1. In the Lookup Transformation Editor screen you can set the Specify how to handle rows with no matching entries field to Fail component and set the job that runs the package to send a notification on job failure. The downsides of this approach are you must run the package from a job schedule, the email that is generated is non-descriptive, and you can only define one operator for this job which may need to be different than your normal operator list (if you are notifying business users instead of IT folks).

2. In the Lookup Transformation Editor screen you can set the component to fail as described in 1 and setup a precedence constraint on the control flow that leads to sending the email on failure. You can also set the MaximumErrorCount for the parent object to 2. Some downsides to this approach include the fact that other errors may occur in the package and the package will still succeed or their may be an error on the source part of the data flow and you may want to handle those kinds of errors separately.

3. In the Lookup Transformation Editor screen you can Redirect rows to no match output, create a variable called RowCount of Data Type Int32, direct the Lookup No Match Output to a Row Count data transformation, send the Lookup Match Output to the normal destination, in the Control Flow set a precedence constraint to the next step where the Evaluation operations is Expression and Constraint where Value is equal to Success and Expression is equal to @[RowCount] == 0, add a Send Mail Task to the Control Flow which has a precendence constraint from the previous step where Value is equal to Success and Expression is equal to @[RowCount] > 1. This will allow you to let the package succeed and send only one email. The downside of this approach is that the data flow destination will still be populated by the matched source data and it won't immediately stop once a No Match is detected. It will only stop once the data flow itself completes.

I hope this meets your business needs. Let me know if you need any further assistance.