referencing Package variables in sp_send_dbmail

Question

referencing Package variables in sp_send_dbmail

ptheberge

SSC Enthusiast

Points: 189
More actions
August 6, 2008 at 7:09 am

#130617

I am using result set, and looping through each record, generating an email for each record. Each email has a different subject and recipients. The values are stored in variables, but I'm not sure how to reference these variables in an email task. I have tried using the sp_send_dbmail and the Send Mail Task.
Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

you can take advantage of the Expressions in the Send Mail task. select Expressions and then click the ellipsis to select which Property you want to setup an Expression for. In your case, select the ToLine property and then in the Expression, select the User Variable to evaluate out to.

hth

ptheberge SSC Enthusiast Points: 189 More actions · Answer 2

That's what I was trying to do, but when I ran the job, it kept saying that there was no recipient specified. I tested out my SQL to make sure an email address was being selected and it was. Should the variable be defeined as a Package level variable or an Object variable?

ptheberge SSC Enthusiast Points: 189 More actions · Answer 3

It won't let me run this if the To section is blank within the Mail Tab of the Send mail Task. The examples I have seen show this fiewld being left blank at design time and being populated using Expressions at run time. Why wont this work for me? :w00t:

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 4

well, both a package level and object type can be the same variable since you are defining scope (package) and type (object), but that is neither here nor there for the issue.

the variable that you should be using in the expression should be String variable which is to be populated via your looping task.

let me take a stab at your design, are you working with a For Each Loop task and which is being run via an Execute SQL Statement to populate an Object type variable. That object variable is the input of the For Each loop?

If so, you will want to ensure that you are mapping a string variable to get the Recipients in the Variable Mappings of the For Each loop. The mapping will be from the field in the recordset to the newly created String variable.

In the Expressions for the Send Mail task, map the ToLine property to the string variable.

If I am way off base on the design, please let me know what you are doing then to initiate the loop process.

Mukti SSCrazy Points: 2826 More actions · Answer 5

Try setting the DelayValidation = True on Send Mail Item. I think its trying to validate it before the variables are set.

HTH

~Mukti