Send Mail Task - Dynamic data - 1000+ emails with attachments

Question

Send Mail Task - Dynamic data - 1000+ emails with attachments

dbdev24

SSC Enthusiast

Points: 125
More actions
March 27, 2010 at 8:57 pm

#156826

Hi Guys,
I need your help with Send Mail Task in SSIS.
I have the email IDs (1000+) and member details in SQL table which I would like to use to send emails using this task.
EmailTo: Dynamic (From the SQL table)
EmailFrom: Default (one email to all)
Subject: Default
Attachments: Dynamic (PDF files in a folder - saved as "[FirstName]_[MemberID]"). This path is stored in one of the columns in SQL.
Message Body: Dynamic (Only [Title].[FirstName] [Lastname]). Rest of the body is same for all.
Do I need to use script task?
Is there a way to map sql tables (Execute SQL task) to the variables in Email task?
My Result Set in EmailTask is grayed out to do the mapping. Not sure why?
Even if I get the mapping done, how do I insert member details (names) into the body dynamically?
Thanks in advance.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You could do it pretty easily in a for-each-loop. However, I would say to fully format all the data such as such as the body and the attachment name with path in the SQL query. Also if there is a way to split up the list into natural breaks you might want to have more than one F-E-L running, thatway you could send out more than one email at a time.

CEWII

dbdev24 SSC Enthusiast Points: 125 More actions · Answer 2

Thanks Elliott.

But can you please explain in detail?

Sorry, I am new to these tools in SSIS.

Please correct me if i am wrong:

1. First add Execute SQL Task, connecting to the table where the email details are.

2. Add For Each Loop?

3. Add Send Email Task?

4. Where do I specify the parameter mappings? eg; Take EmailTo from this column of the table; Take Attachments from that column of the table, etc.

If you could please provide me the steps that I need to follow that would be very much appreciated.

Many thanks.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You are on the rigt track.

You need a variable of type object. You use that object to hold the resultset from the execute SQL task. That object is what the F-E-L iterates through. You need a variable for each item in the resultset that you are passing into the send mail task. You do the mapping in the F-E-L Variable Mappings tab. You do this by index. The first field is index 0 (zero). Basically you are saying that this field in the resultset is mapped to this variable. You place the Send Mail IN the F-E-L. You configure expressions in the Send Mail to set the options.

If you need additional detail please let me know.

CEWII