Now, Design your Package so that it updates status for each task upon completion as shown in screen below.
C#.Net Code: To be used inside Script Task Control.
public void Main()
{
try
{
Dts.Variables["User::EndTime"].Value = DateTime.Now.ToString();
string[] StatusDetails = new string[9];
string[] StatusDetailsUpdate = new string[9];
StatusDetails[0] = " Dimension Load ";
StatusDetails[1] = " Fact Load ";
StatusDetails[2] = " SSAS Dimension Processing - Employee ";
StatusDetails[3] = " SSAS Dimension Processing - Organization ";
StatusDetails[4] = " SSAS Dimension Processing - Function ";
StatusDetails[5] = " SSAS Dimension Processing - Region ";
StatusDetails[6] = " SSAS Dimension Processing - Exit Reason ";
StatusDetails[7] = " SSAS Dimension Processing - Time ";
StatusDetails[8] = " SSAS Cube Processing - HR Exit ";
StatusDetailsUpdate[0] = Dts.Variables["User::Status_Dimension"].Value.ToString();
StatusDetailsUpdate[1] = Dts.Variables["User::Status_Fact"].Value.ToString();
StatusDetailsUpdate[2] = Dts.Variables["User::Status_Dim_Employee"].Value.ToString();
StatusDetailsUpdate[3] = Dts.Variables["User::Status_Dim_Department"].Value.ToString();
StatusDetailsUpdate[4] = Dts.Variables["User::Status_Dim_JobTitle"].Value.ToString();
StatusDetailsUpdate[5] = Dts.Variables["User::Status_Dim_City"].Value.ToString();
StatusDetailsUpdate[6] = Dts.Variables["User::Status_Dim_ExitReason"].Value.ToString();
StatusDetailsUpdate[7] = Dts.Variables["User::Status_Dim_Time"].Value.ToString();
StatusDetailsUpdate[8] = Dts.Variables["User::Status_Exit_Cube"].Value.ToString();
String EmailBody = "<html>";
EmailBody += "<body>";
EmailBody += "<p>";
EmailBody += "Hi,<br/> <br/>";
EmailBody += Dts.Variables["User::MessageText"].Value.ToString();
EmailBody += "<br/><br/>" + "Execution Status : " + Dts.Variables["User::Status"].Value.ToString() + "";
EmailBody += "<br/>" + "Package Executed On : " + Dts.Variables["System::MachineName"].Value.ToString();
EmailBody += "<br/>" + "Package Executed By : " + Dts.Variables["System::UserName"].Value.ToString();
EmailBody += "<br/>" + "Start Time : " + Dts.Variables["System::StartTime"].Value.ToString();
EmailBody += "<br/>" + "End Time: " + Dts.Variables["User::EndTime"].Value.ToString();
//EmailBody += "<br/>" + "Share Point Site Name : " + Dts.Variables["User::SharePoint_Site_Name"].Value.ToString();
EmailBody += "<br/>" + "Source DB Server Name : " + Dts.Variables["User::SourceDB_ServerName"].Value.ToString();
EmailBody += "<br/>" + "Source DB Name : " + Dts.Variables["User::SourceDB_Name"].Value.ToString();
EmailBody += "<br/>" + "Destination DB Server Name : " + Dts.Variables["User::DestDB_ServerName"].Value.ToString();
EmailBody += "<br/>" + "Destination DB Name : " + Dts.Variables["User::DestDB_Name"].Value.ToString();
EmailBody += "<br/>" + "Package Name : " + Dts.Variables["System::PackageName"].Value.ToString();
EmailBody += "<br/>" + "Execution Instance GUID : " + Dts.Variables["System::ExecutionInstanceGUID"].Value.ToString();
EmailBody += "<br/><br/>" + "<B>Detail Status :<B>";
EmailBody += "<br/>";
EmailBody += "<table style=" + "\"" + "border: thin solid #000000; visibility: visible; table-layout: auto; border-collapse: collapse; border-spacing: inherit; empty-cells: show; caption-side: top;" + "\"" + " rules=" + "\"" + "all" + "border=" + "\"" + "0" + "\"" + "\"" + " cellpadding=" + "\"" + "1" + "\"" + " cellspacing=" + "\"" + "1" + "\"" + ">";
for (int i = 0; i < StatusDetails.Length; i++)
{
EmailBody += "<tr>";
if (i == StatusDetails.Length - 1)
{
EmailBody += "<td style=" + "\"" + "border-color: #000000; border-style: solid solid solid solid; border-width: thin;" + "\"" + ">" + StatusDetails[i] + "</td>";
EmailBody += "<td style=" + "\"" + "border-color: #000000; border-style: solid solid solid solid; border-width: thin;" + "\"" + ">" + StatusDetailsUpdate[i] + "</td>";
}
else
{
EmailBody += "<td style=" + "\"" + "border-color: #000000; border-style: solid solid none solid; border-width: thin;" + "\"" + ">" + StatusDetails[i] + "</td>";
EmailBody += "<td style=" + "\"" + "border-color: #000000; border-style: solid solid none solid; border-width: thin;" + "\"" + ">" + StatusDetailsUpdate[i] + "</td>";
}
EmailBody += "</tr>";
}
EmailBody += "</table>";
EmailBody += "</p>";
EmailBody += "<p>";
string ErrorDetails = Dts.Variables["User::ErrorDetails"].Value.ToString();
if (!string.IsNullOrEmpty(ErrorDetails))
{
EmailBody += "<br/> Error Details : <br/>";
EmailBody += ErrorDetails;
}
EmailBody += "<br/><br/>" + "Regards";
EmailBody += "<br/>" + "EXIT BI Team";
EmailBody += "</p>";
EmailBody += "</body>";
EmailBody += "</html>";
SmtpClient clnt = new SmtpClient(Dts.Variables["User::SMTPServer"].Value.ToString());
MailMessage msg = new MailMessage(Dts.Variables["User::FromAddress"].Value.ToString(), Dts.Variables["User::ToAddress"].Value.ToString(), Dts.Variables["User::Subject"].Value.ToString() +" :: "+ Dts.Variables["User::Status"].Value.ToString(), EmailBody);
msg.CC.Add(new MailAddress(Dts.Variables["User::CCAddress"].Value.ToString()));
msg.IsBodyHtml = true;
clnt.Credentials = CredentialCache.DefaultNetworkCredentials;
clnt.Send(msg);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
Email Screen
No comments:
Post a Comment