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.
Read: Implementation of slowly changing Dimension Type-0 ThePassive method (Ignore updates) using SQL script in SQL server
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;
--Inserting the records in the DimCustomer table if any new
records comes
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.
Read: Process the non 0KB ( non Empty ) files and load thedata into database and filter the 0KB (Empty) files from the source Directoryand mail to 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.
