SSIS provides a built-in "Send Mail Task" to send an email. The Send Mail Task is quite simple and straight forward in its
configuration and use.
Using Send mail task we can’t send mail in format. For
sending formatted mail we need to use Script task.
Let’s see in this demo
We need to send the mail to the organization for the best
employee mail. The best employee of the month is selected based on his score. This
Score is calculated based on the various parameter. HR team wants to send the
mail every month-end date automatically. We need to automate this task.
Our mail like
Let’s create the package to compose this mail using the
script task.
First of all we need to get the details of the employee.
See the employee table
|
create table emp
(ID int identity(1,1) primary key,
E_name varchar(50) null,
E_mobile varchar(10) null,
E_add varchar(50) null,
Score int )
Insert emp (E_name,E_Mobile,E_add,Score) values ('Bagesh','888880XXXX','Delhi',98)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Rajesh','888880XXXX','Delhi',70)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Mahesh','888880XXXX','Delhi',34)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Ganesh','888880XXXX','Delhi',67)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Pramod','888880XXXX','Delhi',56)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Prem','888880XXXX','Delhi',89)
Insert emp (E_name,E_Mobile,E_add,Score) values ('Amit','888880XXXX','Delhi',34)
|
From
this we need to get the Highest scored employee.
|
With Highest_Score
AS
(
select ID,E_name,Score,
DENSE_RANK() over (order by Score desc) as score_Rank from Emp
)
select ID,E_name,Score from Highest_Score where score_Rank=1
|
All
employee’s photos are stored in the shared folder with the Employee ID
Now I
am taking script task and writing the code to compose this mail.
In script
task I am writing the below code.
|
string E_name= string.Empty;
int ID=0;
int Score=0;
string smtpAddress = "smtp.gmail.com";
int portNumber = 587;
bool enableSSL = true;
string emailFromAddress =
Dts.Variables["User::Email_id_sender"].Value.ToString(); //Sender
Email Address
string password =
Dts.Variables["User::Mail_credentials"].Value.ToString(); ;
//Sender Password
string emailToAddress = Dts.Variables["User::Email_id_reciver"].Value.ToString();
//Receiver Email Address
string subject = "*******Best Employee of the Month : "+DateTime.Now.AddMonths(-1).ToString("MMMM") +" " + DateTime.Now.Year.ToString() + " *******";
DataTable dt = new DataTable();
var oleDa = new OleDbDataAdapter();
string body = "";
oleDa.Fill(dt,
Dts.Variables["User::obj_emp"].Value);
foreach (DataRow row in dt.Rows)
{
ID = int.Parse(row.ItemArray[0].ToString());
E_name =
row.ItemArray[1].ToString();
Score = int.Parse(row.ItemArray[2].ToString());
}
string ImagePath = @"J:\SSIS1\Photo\" + ID + ".jpg";
using (MailMessage mail = new MailMessage())
{
mail.From
= new MailAddress(emailFromAddress);
mail.To.Add(emailToAddress);
mail.Subject = subject;
mail.Body
= body;
mail.IsBodyHtml = true;
mail.Body
+= "<html><head><title></title></head>";
mail.Body
+="<body> Hi Team,<br/><br/>Best Employee
for the Month of "+ DateTime.Now.AddMonths(-1).ToString("MMMM") + " " + DateTime.Now.Year.ToString() + "<br/>";
mail.Body
+= "Emp ID: " + ID+"<br/>";
mail.Body
+= "Emp Name: " + E_name + "<br />";
mail.Body
+= "Score: " + Score+ " <br/>";
mail.Body
+= "Photo <br/>";
mail.Body += "<img src = "+ ImagePath+ ">";
mail.Body
+= "<br/><br/>";
mail.Body
+= "Best wishes Bagesh!keep it up!!!";
mail.Body
+= "<br/><br/>";
mail.Body
+= "Thanks & amp; Regards,<br/>";
mail.Body
+= "HR Team <br/>";
mail.Body
+= "XYZ</body>";
mail.Body
+= "</html>";
using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))
{
smtp.Credentials = new
System.Net.NetworkCredential(emailFromAddress, password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail);
}
}
|
Package
is ready to run
Now I am executing this package.
We will get the mail like this.
Now check the mail.
Got the mail in my inbox.
See the email body
Get the expected mail. Thanks!!!
No comments:
Post a Comment
If you have any doubt, please let me know.