April 6, 2012 at 11:31 am
I'm new to using SSIS and I'm trying to figure out how to run a query, get a bunch of email addresses from the database, and then send an email to each of those addresses individually. Then I will need to schedule a task that will do this monthly. I set up a Send Mail task, and I have it sending emails to me, so that works, but I am not sure how to get a list of emails, then loop through the list and send each one a separate email.
I set up an Execute SQL Task, which I'm not sure how to check to see if it got the results I wanted, and I'm assuming I need to use a Foreach Loop Container to loop through the results?
How do I loop through the results and send each one an email? Thanks
April 6, 2012 at 11:54 am
rortizsoftware (4/6/2012)
I'm new to using SSIS and I'm trying to figure out how to run a query, get a bunch of email addresses from the database, and then send an email to each of those addresses individually. Then I will need to schedule a task that will do this monthly. I set up a Send Mail task, and I have it sending emails to me, so that works, but I am not sure how to get a list of emails, then loop through the list and send each one a separate email.I set up an Execute SQL Task, which I'm not sure how to check to see if it got the results I wanted, and I'm assuming I need to use a Foreach Loop Container to loop through the results?
How do I loop through the results and send each one an email?
I wouldn't use SSIS to accomplish that, I would do it by writting a stored procedure then schedule the monthly job.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 6, 2012 at 11:58 am
I can write a stored procedure to get the emails from the database, but what about looping throught that list of emails and sending an email to each one separately? Do I basically need to write a program in .Net and have SSIS just call that program somehow?
April 6, 2012 at 12:01 pm
You can use Database Mail to send the emails.
April 6, 2012 at 12:02 pm
PaulB-TheOneAndOnly (4/6/2012)
rortizsoftware (4/6/2012)
I'm new to using SSIS and I'm trying to figure out how to run a query, get a bunch of email addresses from the database, and then send an email to each of those addresses individually. Then I will need to schedule a task that will do this monthly. I set up a Send Mail task, and I have it sending emails to me, so that works, but I am not sure how to get a list of emails, then loop through the list and send each one a separate email.I set up an Execute SQL Task, which I'm not sure how to check to see if it got the results I wanted, and I'm assuming I need to use a Foreach Loop Container to loop through the results?
How do I loop through the results and send each one an email?
I wouldn't use SSIS to accomplish that, I would do it by writting a stored procedure then schedule the monthly job.
Same storedproc can do both... gather the list and send out the emails.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 6, 2012 at 12:04 pm
Usually I do everything in .net, so I'm not familiar with sending out emails from a stored proc. I'll have to research that. Do you have any examples on how to do that?
April 6, 2012 at 12:07 pm
rortizsoftware (4/6/2012)
Usually I do everything in .net, so I'm not familiar with sending out emails from a stored proc. I'll have to research that. Do you have any examples on how to do that?
System storedproc sp_send_dbmail does the trick but, if you feel more comfortable doing it on .Net go for it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 8, 2012 at 6:31 am
Hi,
Place a Execute SQL Task and get the email id's
Assign the result set to a object variable.
Next place a Script Task and write code for sending mails.
use the object variable to get the list of mail id's from the result set.
Hope this helps
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply