SSIS SMTP Connection Reestablish Connection after every Foreach Loop

Question

I have a foreach loop in SSIS that sends an email for every record that it loops through. I'm getting a SMTP connection timeout error after the job runs for 10 minutes, which is the timeout setting on the exchange server.

Is there any way for the connection in the SSIS package to re-establish after every iteration of the foreach loop? I'm on sqlserver 2014.

Answer 1

One option would be to call SQL Server sp_sendmail but this may not be acceptable. You could try and hack the smtp connection to reset by using a expression for the Connection String and then changing a variable in your foreach loop that is used to build the connectstring- i haven't tried this though. Worth a try!