The field [dbo].[Precedence].[JobNotify] in the LeapFrogBI console database is used by the poll job to determine which email to send and when to send it. The package named “BI_Nofity.dtsx” (step 2 of the poll agent job) consumes and sets these values. Here’s what the values represent.
JobNotify Value Legend
0 = No email has been sent
When the load process is reset, JobNotify gets reset to 0. This is the trigger for sending the “Begin” email.
1 = Begin email sent successfully
When the “Begin” email is sent successfully, JobNotify is set to 1.
-1 = Begin email failed to send successfully
When the “Begin” email fails to send successfully JobNotify is set to -1.
2 = Failure email sent successfully
If a task that is not allowed to fail does fail, then a failure email is sent. If this email sends successfully, then JobNotify is set to 2.
-2 = Failure email failed to send successfully
If a task that is not allowed to fail does fail, then a failure email is sent. If this email fails to send successfully, then JobNotify is set to -2.
3 = Complete email sent successfully
When all tasks that are required to complete do complete successfully, then a complete email is sent. If this email send successfully, then JobNotify is set to 3.
-3 = Complete email failed to send successfully
When all tasks that are required to complete do complete successfully, then a complete email is sent. If this email fails to send successfully, then JobNotify is set to -3.
The notification process looks for the lowest value in the list above and acts accordingly. Note that this is a field maintained by LeapFrogBI for the exclusive purpose of determining which email has been sent, which email to send, and when to send it.
Like all things created by LeapFrogBI the code used to carry out the notification process is contained in a SSIS package (in this case BI_Notify.dtsx). You are free to investigate the package and customize it to meet your specific needs. If you do customize BI_Notify, then you should save the custom package in a folder outside of the LFBI deployment directory. This will prevent future deployments from overwriting your customizations. Also, update step 2 of the SQL agent poll job to point to your customized package.
Enjoy!
