custom MessageSource in SSIS send mail task

Question

in an ssis package, I have a Send Mail Task and in the email body, I have a text and I want to add to that body the hostname. I've creates my variable hostname with the value EXEC('select HOST_NAME()') and I've added an expression for MessageSource:

"This is an automated message.  You do not need to do anything with it." + @[User::hostName]

but in the email, the text is:

This is an automated message. You do not need to do anything with it.EXEC('select HOST_NAME()')

How to make it to receive the hostname in the email?

Best regards

Answer 1

I usually use an Execute SQL task here to run SELECT HOST_NAME()

Set the result set property to "single row", then configure the result set from the left hand menu to map the result into a variable, use the variable value in your e-mail.

Answer 2

You can use a execute SQL Task and use *select HOST_NAME()* into the task.This will give you the result on execution and the resultset can be stored in the variable [User::hostName].

So as a result you wont be changing anything in the expression and the hostname will be easily used.

Let me know for any further clarification on the same