I have DTS package that send HTML emails to a list of recipient each night. After determining the recipients, I loop through them, create an HTML email via ".CreateMHTMLBody [HTML page URL]" and send.
On average, my package should be sending out about 175 emails per night.
For the past several weeks, the Job runs, sends out about 125 (give or take a few) emails and then FAILS. The error message logged is this:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:ActiveX Scripting encountered a Run Time Error during the execution of the script.
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
If go into the job and reschedule it to run again, it continues with the remaining emails and completes successfully.
I have tried running tests of up to 250 emails at once, but when I run the tests, nothing fails.
I have tried setting the job to run at different times (5:00, 6:15am, 6:30am) but each time it fails about 3/4 of the way through.
I've tried using DCOMcnfg to make sure my SQLAgent login acct is allowed acces under DefaultAccessPermissions. No help.
I've tried forcing a 60-second delay after 75 emails (in case I was somehow clogging the exchange server). No help.
I've tried forcing a 2-second delay between each email (in case our web server was having trouble serving up the HTML page requested by the CreateMHTMLBody command each time). No help.
I'm pulling my hair out and I'm out of ideas and almost out of hair.
Has ANYBODY encountered ANYTHING like this before? HELP!!!!!