SSIS Double Sending Emails

  • 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?

  • 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, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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