HOW TO SEND EMAIL NOTIFICATION? THE EMAIL ADDRESS SHOULD PICK FROM DATABASE TABLE?

My suggestion would be to create variables in SSIS.  Fill those variables from your database and then use "Expressions" in the "Send Mail Task Editor" to set the necessary fields to your variables.

You can use Execute SQL Task to pull your data from your database and set the variables.

Link below shows some basics of setting and using variables.  
http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

That's easy, use execute sql task to get you the desired fields into variables and use it in the send mail task.
0.png
1.png
2.png
3.png
4.png
5.png
6.png
7.png

carsRST, huslayer

This is very helpful. But my table have two column.
ctrl_Name and ctrl_value
========        ========
EmailTo            abc@gmail.com
EmailFrom        xyz@gmail.com
emailBody       thank you


and I have to pick from rows not from the column. Thank you very much this will help me to start

and the body of message is suppose to pic from the return value of store_proc. where I am using execute sql task. Any suggestion how to get body of message. I tried from script task but it returns error neither body of msg is picking nor my email to and email_from??

If you didn't get the answer, why did you accept a solution?

In screen 6 & 7 the SP will run and return the body to a single row, or you can change that as it fits you .

there's many way to get the Variables populated, script task, concatenate strings or even selecting it in 3 execute sql tasks !

let me know where you're stuck at? and what you did so far.. and what you're expecting for the BODY

>>and the body of message is suppose to pic from the return value of store_proc.

You'll execute your stored procedure in the Execute SQL Task.  That stored procedure will return a value, to which you'll set to a variable.

See link below.  Has all the pictures you want on how to set variables from an Execute SQL Task.
http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

PS--you need to make sure you have a full answer before pushing the accept solution.
544-image004.jpg

As I stated in the first post to this thread, you'll take that variable and use the "expressions" in the mail task to set the body of your message.

See image below for example on how/where to set "expressions" in the mail task.

http://amenjonathan.wordpress.com/2011/01/10/creating-a-solid-ssis-etl-solution-part-4-email-task-and-package-execution-report/


 email-task-editor-expressions.jpg

Srry for all trouble, I thought accepting solution make faster to get quick answer. since im getting different email from expert-exchange.

What Im trying to here is I have two store_proc. One is to upload the data from staging to array_table and return the value in integer. Which I specify in my variables as returnValue.

Other store_proc have email_To and Email_From rows which I have to pick from table. And Table look like this :
ctrl_Name         ctrl_value
========        ========
EmailTo            abc@gmail.com
EmailFrom        xyz@gmail.com
emailBody       thank you

I used both way calling store_proc and directly using
SELECT [Ctrl_Value]
FROM [table_1]
where Ctrl_Name = 'Email_From'

And I am following same procedure as on the pics 0-7. I got error like this:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "strFrom": "The type of the value being assigned to variable "User::strFrom" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

=====
Error: The type of the value being assigned to variable "User::strFrom" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Check the data type of your variables.  Make sure they are strings.

OK, look at screen 1 and configure your variable the way its over there, it must be strings (TEXT) type.

and since you're selecting from ROWS, this article will help you to

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_2886-How-to-use-Script-Component-as-Asynchronous-Transformation.html

let me know if you need more help.

I check all the variables are strings except return value which is integer because my first store_proc when execute it will only gives numeric. So I added script task for body of message to strings


 

then change it to int16, int32, and int64 till the error stops and you find out what data type you're returning, unless you know from the SP.

This is the error messages I got in execute sql task:
Error: An error occurred while assigning a value to variables "strTo": "item cannot be found in the collection corresponding to the requested name or ordinal".

Thanks huslayer, I am trying to change each and evey data types. On database table is define as vachar (50) for email_From and varchar(max) for email_To.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Email_From": "Unable to find column Email_From in the result set.".
 
Do I have to defined parameter too or just Result set in execute sql task 4 & 6.
?? Help this new bie

No, Result set is the one!

In screen 5, I named the returned columns "EmailFrom" and "EmailTo", So Try to Alias your column to have the same name  as what you're typing there, in the Result set I mean  :)

srry for late respond huslayer. I mapped with returned columns "emailFrom" and "EmailTo" but it keep saying column is not found in database.

Here is my table and here is the queries I wrote in execute sql task:
ctrl_Name         ctrl_value
========        ========
EmailTo            abc@gmail.com
EmailFrom        xyz@gmail.com
emailBody       thank you

SELECT ctrl_value
FROM Table_1
WHERE ctrl_Name = "EmailTo"

And I add one more execute sql task for "EmailFrom"

SELECT ctrl_value
FROM Table_1
WHERE ctrl_Name = "EmailFrom"

And named returned columns as "EmailFrom" and "EmailTo".
any suggestion please

Try that...

SELECT ctrl_value as EmailTo
FROM Table_1
WHERE ctrl_Name = 'EmailTo'

Huslayer, I tried this one too. It gives right value in SSMS but when I put this value and send to variables it gives me error messages:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Email_To"; "The type of the value being assigned to variable "User::Email_To" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type object.

I attached the pics. Now tell me where it went wrong? Is it ADO connection is different then OLEDB? or DelayValidation = True as package level too?
1.jpg
2.jpg
3.jpg
4.jpg
5.jpg
6.jpg

First of all thank you huslayer. It works find on ADO connection when I changed from OLEDB. So I used two connection instead of one. Thanks once again.

You're welcome, glad to hear that.....
See you in another SSIS question :)