Send mail to variable recipients

Question

Send mail to variable recipients

carol-806410

SSC-Addicted

Points: 422
More actions
July 7, 2008 at 2:18 am

#129812

I want to set up an emailing system that a number of different applications can use. The plan is to:
1. Write lines into a SQL table with fields for To, From, Body and Status,
2. Set up a trigger to act on table updates and call some code to send any 'Queued' emails,
3. Change the status to 'Sent'.
I thought to use the Send Mail task in SSIS, however it seems to expect a hard-coded To address. Is there any way to look up the recipient from a table? Or will I have to use a Script task and write something in VB.NET?
Or should I just forget triggers and SSIS and write it as a Windows service in VB.NET?
Thanks,
Carol

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You should forget triggers and use service broker. It is designed for creating a queue and processing that queue as requests come in.

Which then brings me to the most important question - why are you wirting any code? sp_send_dbmail is doing exactly what you are talking about. It adds a mail message to a queue and processes it as soon as the queue reader is ready. It is asynchronous, uses service broker, and fully supported by Microsoft.

carol-806410 SSC-Addicted Points: 422 More actions · Answer 2

Why am I not using sp_send_dbmail? Mosty because I'd never heard of it 😉

Thanks for the tip!

Carol

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

It is the replacement for xp_SendMail. It uses SMTP to send email rather than needing outlook and a MAPI profile.

It works pretty well - look it up in BOL.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 4

Just and FYI, you can send mail from SSIS.

1) Use an Execute SQL Task to pull the variabels

2) Create a Send Mail Task; set Expressions to map the variables to the correct properties of the task.

dbajunior SSCrazy Points: 2307 More actions · Answer 5

Please go here to learn more about sp_send_dbmail:

http://msdn.microsoft.com/en-us/library/ms190307.aspx

carol-806410 SSC-Addicted Points: 422 More actions · Answer 6

Actually this is exactly what I have done. I had a look at sp_send_dbmail but I couldn't see how you set the "from" value. This mechanism will be used by a number of different applications, so I wanted to be able to set everything.

I'm delighted with the result. I have a table with the fields email_id, email_from, email_to, email_subject, email_body, email_format (html or text) and email_status.

My SSIS then runs every 5 minutes and deals with all the lines where email_status = 'queued'.

Now all the various apps and scripts will have to do to send an email is to write a line into the table. I think this will work really well! 🙂

Thanks everyone for your comments,

Carol

carol-806410 SSC-Addicted Points: 422 More actions · Answer 7

MentalWhiteNoise (7/8/2008)
Just and FYI, you can send mail from SSIS.
1) Use an Execute SQL Task to pull the variabels
2) Create a Send Mail Task; set Expressions to map the variables to the correct properties of the task.

Actually this is exactly what I have done. I had a look at sp_send_dbmail but I couldn't see how you set the "from" value. This mechanism will be used by a number of different applications, so I wanted to be able to set everything.

I'm delighted with the result. I have a table with the fields email_id, email_from, email_to, email_subject, email_body, email_format (html or text) and email_status.

My SSIS then runs every 5 minutes and deals with all the lines where email_status = 'queued'.

Now all the various apps and scripts will have to do to send an email is to write a line into the table.

The only thing to note is that I am only retrieving the id from the Execute SQL task and looking the other values up from within the script task. This is because I ran into the charater limit on the SSIS string variable when trying to pass a longish email_body value.

Thanks everyone for your comments,

Carol

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 8

Actually this is exactly what I have done. I had a look at sp_send_dbmail but I couldn't see how you set the "from" value. This mechanism will be used by a number of different applications, so I wanted to be able to set everything.

I think you will need to create a profile for each "from" (application) and assign the @profile_name parameter in sp_send_dbmail based on the application.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.