In previous post we saw that how to handle the SCD Type 0. In this post we will see how to load the data into the DimCustomer table and send the notification to the business for the manual intervention.
https://bageshkumarbagi-msbi.blogspot.com/2022/05/implementation-of-slowly-changing.html
In the table taking Execute SQL task and doing the insert
& ignore records in DimCustomer and inserting the records into the audit
table.
Taking Execute SQL Task and doing the configuration
Below is the SQL Script
|
--Inserting the records in the Audit table if any changes in
PAN_Num or Aadhar_Num INSERT INTO audit_tbl (app_nm, audit_msg) SELECT 'DimCustomer Load' AS App_Nm, 'ID : ' + Cast(STG.cus_id AS VARCHAR(20)) + ', Cus_Nm : ' + STG.cus_nm + ', PAN_Num : ' + STG.pan_num + ', Aadhar_Num : ' + STG.aadhar_num AS Audit_Msg FROM dimcustomer_stg
stg LEFT JOIN dimcustomer dim ON dim.cus_id = stg.cus_id WHERE dim.pan_num <> stg.pan_num OR dim.aadhar_num <> stg.aadhar_num; INSERT INTO dimcustomer (cus_id, cus_nm, pan_num, aadhar_num) SELECT STG.cus_id, STG.cus_nm, STG.pan_num, STG.aadhar_num FROM
dimcustomer_stg stg LEFT JOIN dimcustomer dim ON dim.cus_id = stg.cus_id WHERE dim.dimcustomer_id IS NULL; |
Now we are taking another Execute SQL task which stores the information about the Audit record and storing that in the Object variable.
Now taking Script task to send the mail to the business.
https://bageshkumarbagi-msbi.blogspot.com/2019/01/process-non-0kb-non-empty-files-and.html
Below C# code we are using to send the mail to the business.
|
public void Main() { // TODO: Add your code here string smtpAddress = "smtp.gmail.com"; int portNumber = 587; bool enableSSL = true; string emailFromAddress = "***************@gmail.com"; //Sender Email Address string password = "*************"; //Sender Password string emailToAddress = "**************@gmail.com"; //Receiver Email Address string subject = "Request for PAN or Aadhar Number update for the
Customer"; string body = "Hello Admin, we have request to update the PAN or Aadhar
number update for below Customer Please have a look."; DataTable dt =
new DataTable(); var oleDa = new OleDbDataAdapter(); oleDa.Fill(dt,
Dts.Variables["User::Audit_Rec"].Value); if (dt.Rows.Count > 0) { 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>"; mail.Body
+= "<tr>";
mail.Body += "<td>Customer
details</td>";
mail.Body += "</tr>"; foreach (DataRow row in dt.Rows) {
mail.Body += "<tr>";
mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";
mail.Body += "</tr>"; }
mail.Body += "</table>";
mail.Body += "Customer details are comma
separated";
mail.Body += "</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; } |
Package looks like below
Before running this package records in the table.
Now running this package.
Package executed successfully. See the records in the table.
Also we are getting the mail for PAN and Aadhar update.
Business will review this request and if needed then they will updated this records manually.
Hey Nice Blog!!! Thank you for sharing information. Wonderful blog & good post.Its really helpful for me, waiting for a more new post. Keep Blogging!!!
ReplyDeletePassive Harmonic Filter
Energy Management System | NAAC Energy Controls