June 26, 2018 at 7:53 am
I have an SSIS package that uses 2 different send mail tasks. One is used for failure, one is used for success. When the job succeeds, it sends 2 emails rather than 1.
The package is used in an agent job that runs every 30 minutes. The agent job checks a request queue, then uses a foreach loop to go through and run a series of other jobs that are not on a schedule. I am running the jobs using the sp_start_job wait stored procedure in an execute sql task. I have attached a picture of the package and how it is laid out.
Does anybody have any idea how I can stop it from sending double emails?
June 26, 2018 at 8:04 am
Those send mail tasks are inside a For Each Loop. If you're getting two emails, I'd guess that the loop condition is set to allow the contents of that container to run a second time. If it fails, the loop is broken, thus the single email when it fails.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
June 26, 2018 at 8:24 am
I agree with Tim. Put the e-mail tasks after the FEL, with the same Success/Fail precedence constraints.
You might also like to consider using an Error Handler for the failures.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 26, 2018 at 8:34 am
After doing some more digging in msdb.dbo.sysjobhistory I see that the jobs that are run by the process are running multiple times. The for each loop is fed from a data set that is populated by a sql query. The sql query looks at the job request table where the status is open. When I look at that table I see a single request for reach job , and the query only returns one result for each job . The foreach Loop Container is set up to use a foreach ADO Enumerator and uses an object variable that is populated by the get Jobs list Data flow task. When running the query from the get jobs list data flow task, I only have 1 reference to each job. I have attached a screenshot of the Foreach loop container.
Upon further investigation, the jobs only seem to be getting run twice when it is run from the schedule on the server. When I run the agent job by manually triggering it form the server, it runs once. When I run the job by running the steps for it in the debugger in Visual studio, it runs once. When it runs from the schedule, it runs twice.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply