Many a times, for
quick understanding of the loads, we want to know how many records were loaded
to each target at the end of current workflow run.
Getting such report in plain text format is easy but you find it as not so reader friendly.
Here I brief you on how to send such report in HTML format using Informatica and UNIX.
Make a simple one-to-one mapping in Informatica which fetches target tables record count for the latest run and store in a CSV file.
Informatica mapping skeleton:
Source Definitionà Source Qualifier àTarget Definition(CSV File as Target)
Typical source qualifier query could be:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/bin/ksh
#######################################################################
#Author: Sathish Manthani
#Usage: record_count_report.ksh <from_email_id> <to_emailid> <tgt_csv_file_path>
#######################################################################
from_email_id=$1
to_email_id=$2
echo "From: ${from_email_id}
To: ${to_email_id}
MIME-Version: 1.0
Subject: Target record count report
Content-Type: text/html" > /tmp/mail_file.$$.txt
echo "
<p>Hi,</p>
<p>Here is the target record count report</p>
<p> </p>
<table border=3 BORDERCOLOR="#080808" bgcolor="#F0F0F0">" >> /tmp/mail_file.$$.txt
while read LINE
do
CLN_LINE=`echo "$LINE" | sed 's/,/<\/td><td>/g'`
echo "<tr><td>${CLN_LINE}</td></tr>">>/tmp/mail_file.$$.txt
done<$3
echo "</table>
<p> </p>
<p> </p>" >>/tmp/mail_file.$$.txt
cat /tmp/mail_file.$$.txt | /usr/sbin/sendmail –t
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The above shell script should be called at ‘post session success command’ (means after successfully creating the CSV file).
Call example: /mount/infa_repo/infa_shared/scripts/record_count_report.ksh frm_email@company.com to_email@company.com /mount/infa_repo/infa_shared/TgtFiles/target_record_count.csv
Hope that helps,
Sathish Manthani
Getting such report in plain text format is easy but you find it as not so reader friendly.
Here I brief you on how to send such report in HTML format using Informatica and UNIX.
Make a simple one-to-one mapping in Informatica which fetches target tables record count for the latest run and store in a CSV file.
Informatica mapping skeleton:
Source Definitionà Source Qualifier àTarget Definition(CSV File as Target)
Typical source qualifier query could be:
SELECT ‘TARGET_1’
TABLE_NAME, COUNT(*) RECORD_COUNT, LOAD_DATE
FROM TARGET_1
WHERE LOAD_DATE=(SELECT
MAX(LOAD_DATE) FROM TARGET_1)
UNION
SELECT ‘TARGET_2’
TABLE_NAME, COUNT(*) RECORD_COUNT, LOAD_DATE
FROM TARGET_2
WHERE LOAD_DATE=(SELECT
MAX(LOAD_DATE) FROM TARGET_2)
…
…
So with the above
mapping/session, you get target counts in a CSV file and now I give you the
script which sends email in HTML format using Korn Shell script:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/bin/ksh
#######################################################################
#Author: Sathish Manthani
#Usage: record_count_report.ksh <from_email_id> <to_emailid> <tgt_csv_file_path>
#######################################################################
from_email_id=$1
to_email_id=$2
echo "From: ${from_email_id}
To: ${to_email_id}
MIME-Version: 1.0
Subject: Target record count report
Content-Type: text/html" > /tmp/mail_file.$$.txt
echo "
<p>Hi,</p>
<p>Here is the target record count report</p>
<p> </p>
<table border=3 BORDERCOLOR="#080808" bgcolor="#F0F0F0">" >> /tmp/mail_file.$$.txt
while read LINE
do
CLN_LINE=`echo "$LINE" | sed 's/,/<\/td><td>/g'`
echo "<tr><td>${CLN_LINE}</td></tr>">>/tmp/mail_file.$$.txt
done<$3
echo "</table>
<p> </p>
<p> </p>" >>/tmp/mail_file.$$.txt
cat /tmp/mail_file.$$.txt | /usr/sbin/sendmail –t
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The above shell script should be called at ‘post session success command’ (means after successfully creating the CSV file).
Call example: /mount/infa_repo/infa_shared/scripts/record_count_report.ksh frm_email@company.com to_email@company.com /mount/infa_repo/infa_shared/TgtFiles/target_record_count.csv
Hope that helps,
Sathish Manthani