Variable reference within Exec SQL calling sp_send_dbmail
-
How do you reference a package varialbe within the Exec SQL step. My syntax is below, but it will not work.
declare @EMAILTO varchar(50)
declare @SUBJ varchar(50)
declare @BODY varchar(2000)
Select @EMAILTO = Dts.Variables("varEmail")
Select @SUBJ = Dts.Variables("varSubject")
Select @BODY = Dts.Variables("varMsgBody")
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'ArchibusProfile',
@recipients= @EMAILTO,
@subject = @SUBJ,
@body = @BODY
-
you need to parameterise your query. replace the code with:
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'ArchibusProfile',
@recipients= ?,
@subject = ?,
@body = ?
Then go to the parameter mapping section of the task and add in the three necessary variables. The parameter name is an integer relating to the position of the parameter in your query. The first parameter would thus be:
Variable Name: varEmail
Direction: Input
Data Type VARCHAR
Parameter Name: 0
Parameter Size: 4000 (at a guess)
The next parameter would have 1 as its name, and so on.
Tom
Life: it twists and turns like a twisty turny thing
-
I found an example like this yesterday and got this to work by using the Paramter Mapping tab and m,apping my variables to a paramter and referencing them with the ?. Thanks for replying!:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply