I am pulling out my hair (what little I have!) trying to accomplish what should be a simple task.
I have an "Execute Sql Task" that sends out an email (see attached code and screendumps). I am trying to use variables to pass values (email addresses, server names, etc) to the task. Sadly, it is not working. If I substitute the variables for hard-coded stings, then it works - the email is sent and received.
Any thoughts on what I've missed out on?
In advance, thanks!!!
declare @user_name varchar(80)
declare @user_email varchar(80)
declare @support_name varchar(80)
declare @support_email varchar(80)
declare @server_name varchar(80)
declare @server_smtp varchar(80)
declare @message_body varchar(1000)
set @message_body =
'Dear ' + @user_name + '\n\nPlease be advised that the most recent attempt to update ' +
'the BranchTargets has failed.' + '\n\nA copy of the errors, named BranchErrors.csv, ' +
'has been placed on the designated network drive and can be opened in Excel. For further ' +
'assistance, please contact ' + @support_name + ' at IT support.' +
'\n\n\nMessage sent by: BranchTarget_Update (SSIS upload package)\n\n'
@vcTo = @user_name,
@vcSubject = 'BranchTarget Update: Failure! XXX',
@vcBody = @message_body,
@vcFrom = 'BranchTarget.Update@realpeople.co.za',
@vcSMTPServer = @server_smtp,
@cSendUsing = '2',
@vcPort = '25',
@cAuthenticate = '0',
@vcSenderName = 'BranchTarget.Update@realpeople.co.za',
@vcServerName = server_name