Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

referencing Package variables in sp_send_dbmail Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2008 6:54 AM
Points: 9, Visits: 21
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.
Post #547474
Posted Wednesday, August 6, 2008 8:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:03 AM
Points: 621, Visits: 348
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
Post #547576
Posted Wednesday, August 6, 2008 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2008 6:54 AM
Points: 9, Visits: 21
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?

Post #547604
Posted Wednesday, August 6, 2008 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2008 6:54 AM
Points: 9, Visits: 21
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?
Post #547619
Posted Wednesday, August 6, 2008 9:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:03 AM
Points: 621, Visits: 348
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.
Post #547625
Posted Wednesday, August 6, 2008 10:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 10:22 AM
Points: 226, Visits: 725
Try setting the DelayValidation = True on Send Mail Item. I think its trying to validate it before the variables are set.

HTH
~Mukti
Post #547719
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse