Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2606
  • Last Modified:

SSIS: Using User variables in Send Mail Task

Hi there,

I am trying to build an email message using Send Mail task but taking some of the message in the email from two user defined variables that I have set in Execute SQL Task (i.e. configuring Result Set to relate my user defined variables to the column names where I'm reading the values from).

The expression I've tried to build in the Send Mail task is below:

"The xxxx xxxxxx report was successfully processed with an open date of: " + @[User::FileDate2] + "and an open time of: " + @[User::FileTime2]

Open in new window


But I get the error is

"The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."

However, it seems to work if I just select the user variable i.e. if I just choose User::FileDate2 then I get the value I'm looking for but obviously no messages around what it means just a date.

What can I do to get the email message I'm looking for?

Thanks,

Segun
0
onesegun
Asked:
onesegun
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
Try putting the body of the message into its own variable, which will allow you to include other variables as part of its definition. Then use that to populate your mail task.
0
 
ValentinoVBI ConsultantCommented:
Are you sure both variables are on package-level scope?  You can check that from the Variables window where one of the columns mentions the scope.
0
 
onesegunAuthor Commented:
Seems like the only way is to join the variables together at the script task level i.e. the message, the time variable and the date variable into one long variable and then use that in the Send Mail Task.

Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now