DBA team is Checking manually database status ONLINE and OFFLINE. It is the tedious task. We have automated this task to read the database information and sending status mail to the DBA team.
SQL Script to get the database status.
We are developing the package to pull the database information and send it to DBA team mail.
SQL Script.
We are storing the full result set into the Object variable.
Now we are taking the Script task to send the mail.
We are using the below Namespace
|
using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Data.OleDb; using System.Net.Mail; using System.Net; |
Code.
|
public void Main() { DataTable dt =
new DataTable(); var oleDa = new OleDbDataAdapter(); oleDa.Fill(dt,
Dts.Variables["User::Database_Details"].Value); string smtpAddress = "smtp.gmail.com"; int portNumber = 587; bool enableSSL = true; string emailFromAddress = "bageshkumarbagimsbi@gmail.com"; //Sender Email Address string password = "************"; //Sender Password string emailToAddress = "bageshkumarbagi@gmail.com"; //Receiver Email Address string subject = " Database Status Report"; { mail.From
= new MailAddress(emailFromAddress);
mail.To.Add(emailToAddress);
mail.Subject = subject; mail.Body = body;
mail.IsBodyHtml = true; mail.Body
+= "<body>"; mail.Body
+= "<table border=2>"; mail.Body
+= "<tr>"; mail.Body += "<td bgcolor=#00FFFF>Server name</td><td
bgcolor=#00FFFF>DataBase Name</td>" + "<td bgcolor =#00FFFF>Status</td>"; mail.Body
+= "</tr>"; {
mail.Body += "<tr>"; if (row.ItemArray[2].ToString() == "ONLINE") {
mail.Body += "<td>" + row.ItemArray[0].ToString() + "</td>"; mail.Body
+= "<td>" +
row.ItemArray[1].ToString() + "</td>";
mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>"; } else {
mail.Body += "<td bgcolor=#FF0000>" + row.ItemArray[0].ToString() + "</td>";
mail.Body += "<td
bgcolor=#FF0000>" + row.ItemArray[1].ToString() +
"</td>";
mail.Body += "<td
bgcolor=#FF0000>" + row.ItemArray[2].ToString() +
"</td>"; mail.Body
+= "<br/><br/>"; mail.Body
+= "Thank and Regards,<br/>"; mail.Body
+= "DBA Team <br/>"; mail.Body
+= "XYZ</body>"; mail.Body
+= "</html>"; {
smtp.Credentials = new NetworkCredential(emailFromAddress,
password);
smtp.EnableSsl = enableSSL;
smtp.Send(mail); } } Dts.TaskResult
= (int)ScriptResults.Success; } |
Now we are running this package.
Package executed successfully.
See the inbox.
Opening the mail.
If the Database is OFFLINE then that row will be RED highlighted. See below
Get the expected Mail.
Sands Casino - Daroote Casino | Daroote
ReplyDeleteInformation and Reviews about 샌즈 카지노 먹튀 Sands Casino 카지노 사이트 주소 at 안전 카지노 사이트 Daroote Casino in Daroote, including gaming tables, games, video poker machines, keno, keno, keno and other Rating: 4.3 · 3 바카라 사이트 주소 reviews 샌즈 카지노