Solved

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

Posted on 2011-09-09
23
410 Views
Last Modified: 2013-11-10
Hello all,

I have to create a package. If the package failed /success then pick the email address "Email To" and "Email_From" from the database table and body of message is pick from stored proc. Any suggestion?
0
Comment
Question by:samthegreat
  • 9
  • 8
  • 5
23 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 36509895
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/
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36509922
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
0
 

Author Comment

by:samthegreat
ID: 36513459
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
0
 

Author Comment

by:samthegreat
ID: 36523657
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??
0
 
LVL 16

Expert Comment

by:carsRST
ID: 36523692
If you didn't get the answer, why did you accept a solution?
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36523819
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
0
 
LVL 16

Expert Comment

by:carsRST
ID: 36524060
>>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
0
 
LVL 16

Expert Comment

by:carsRST
ID: 36524090
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
0
 

Author Comment

by:samthegreat
ID: 36525971
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.

0
 
LVL 16

Expert Comment

by:carsRST
ID: 36526017
Check the data type of your variables.  Make sure they are strings.
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36526503
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

http://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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:samthegreat
ID: 36526921
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


 
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36526984
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.
0
 

Author Comment

by:samthegreat
ID: 36527136
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".
0
 

Author Comment

by:samthegreat
ID: 36528651
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
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36529002
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  :)
0
 

Author Comment

by:samthegreat
ID: 36554948
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
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36556256
Try that...

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

Author Comment

by:samthegreat
ID: 36556475
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
0
 
LVL 21

Accepted Solution

by:
huslayer earned 500 total points
ID: 36559984
what's the expected results from the SQL query in SSMS?
i'm not sure why it doesn't like the string value !!



SELECT convert(varchar(100),ctrl_value) as EmailTo
FROM Table_1
WHERE ctrl_Name = 'EmailTo'
0
 

Author Comment

by:samthegreat
ID: 36818995
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.
0
 
LVL 21

Expert Comment

by:huslayer
ID: 36819382
You're welcome, glad to hear that.....
See you in another SSIS question :)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now