Microsoft Business Intelligence

Monday, 13 December 2021

Send the mail to DBA Team about Database Status OFFLINE/ONLINE

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";

string body = " ";

body = "Hello DBA Team,";

body = body + Environment.NewLine + Environment.NewLine + " Report deatil as below.";

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>";

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>";

foreach (DataRow row in dt.Rows)

{

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 += "</tr>";

}

mail.Body += "</table>";

mail.Body += "<br/><br/>";

mail.Body += "Thank and Regards,<br/>";

mail.Body += "DBA Team <br/>";

mail.Body += "XYZ</body>";

mail.Body += "</html>";

using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))

{

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.

Microsoft Business Intelligence

Pages

Monday, 13 December 2021

Send the mail to DBA Team about Database Status OFFLINE/ONLINE

Popular Posts