Using reusable DTS package to send mail
-
I have a rather large stored procedure code base that uses many xp_sendmail commands to email notifications and reports. I am looking for a solution that uses DTS, but is parameterized so that email addresses and attachments can be passed into one central DTS package, and this DTS package will send mail. Has anyone come across a solution that would do this? Any help would be much appreciated.
Regards,
ajroney
-
Without knowing your requirements this is a difficult question to answer, but I thought that I might offer these options.
Any process that would need to send an email could populate a table via stored proc that would add a record with a sequence number to that table.
Your DTS package could be scheduled to execute on a timed schedule (like every hour for example). The DTS Package would read in the table and send the messages and delete each message after successful send.
Another option, and more difficult to impliment would be to load the DTS Package via Active-X script, DTSRun or other COM aware executable, set the global variables of the Package and finally execute it.
The second option could get out of hand based on the volumn of messages you will be sending, for a new instance of the package will be in memory for each email you send. The first option offers a way to scale the solution and assure that the scheduled task does not attempt to execute a second time prior to the completion of the first.
-Mike Gercevich
-
Why the need to use DTS?
Looking at Mike's first option, wouldn't a scheduled job to execute xp_sendmail (or my preferred replacement xp_smtp_sendmail) work just as well and with a lot less overhead?
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply