I have an interesting issue. I will try to keep it simple as explanation.
- I have a SSIS package which has an Event Handler (
OnError, including some extra logging info) with Send Mail Task which is working just fine when the package fails - Database Mail is configured and Job Failure notifications are enabled in SQL Server Agent.
The idea behind that is because I use proxy to run the SSIS package and when the proxy is not working I need to receive email with error (which is now working). When the job starts it cannot go to SSIS OnError handler and if I'm not checking the jobs I will never know If the job is working or not.
Now when I have proxy problem I receive only one mail which is fine but when something in the SSIS package fails I receive 2 mails: one from SQL Server Agent notification, and another one from the OnError event handler from SSIS, which is something I'm trying to avoid.
I need only 1 email when an error occurs. Is it possible to prioritize it somehow and when the SQL job notification is sending mail another one from SSIS to be ignored?
I'm thinking to create one sample SSIS package only with one SQL Task and include it before all the SSIS steps just to check If the job is able to start. I don't know. I don't like the idea.
Do you have any ideas? Thanks.
Best regards.
What do you mean by the Proxy isn't working?
If you can define and test that, add a job step that does proxy test, and emails with send_dbmail as step 1. Keep the SSIS package as step 2 with it's inbuilt error notification, and disable the job notifications.