Row Counts and other log info need to sent out as an e-mail attachment

[ProDev]

Hi All,

I am loading data from flat files to Oracle tables. I got is to send single E-Mail notification at end of the day, to a user group describing the detail like the No of Rows got inserted, no of rows got updated, no of rows read, job start /end time, status ect.

My mapping is routing data to target table as well as to reject table (based on validity of data). I created a table "T_JOB_PROGRESS" with following fields

Job_Execution_ID (might be a SEQ no)
Job_Name (mostly will be the Table Name itself)
Job_ID (lets have an ID or so for each table)
Job_Status ("Success" or "Fail&quot
Job_Start_Date (mostly will be the start of session)
Job_End_Date (let it be the end of session)
Job_Result
Job_Description (will be string containing the No of Insert/Update/Reject rows info which will be used to prepare content of e-mail later on)
Rows_Read
Rows_Inserted
Row_Updated
Rows_Rejected_Updated
Rows_Rejected_Inserted

My final mapping will load data from "T_JOB_PROGRESS" to a Flat File which will be sent through E-Mail as an attachment to user group.

Now, can anyone help, letting me know how to get "T_JOB_PROGRESS" loaded with all such information ?
I tried and could take COUNT(*) (using Aggregator transformation) for Update Strategy transformation that was inserting to target table.
But the other Aggregator transformation for Update Strategy (used for updating target table) needs one more row to get inserted in "T_JOB_PROGRESS".

However, please give to me some clue to handle such situation.
Basically, I have to send only one e-mail at end of day summarizing load detail of all sessions. And I am trying to make a single entry to "T_JOB_PROGRESS" for this purpose.

[I have 4 Update strategies in each mapping (2 for target table update and Insert, other 2 for reject table update and Insert. As I wrote before the Aggregators do count the respective rows and makes fresh insert in "T_JOB_PROGRESS".]


Thanks

 

[blom0344]

Getting the data into the T_JOB_PROGRESS seems like a job for a datbase-trigger. However, it seems to me a lot of the information is simply not available at database level. If it exists at all it is in the logging files, which may be a better idea to sent (with some detailed instructions how to read them)..........


T. Blom
Information analyst
tbl@shimano-eu.com

 

[ProDev]

T. Blom,

Won't a log file be a too big one with much more unnecessary information as well ?

 

[blom0344]

Well, If I look at the logfiles for different sessions they range in size (with for transformations normal logging) from 5- 25 kB. Of course , if you set verbose logging on , even for just one transformation they will become very large.
But looking at the contents of such a log file there is quite some information available. Possibly not all you need, but it is generated anyhow, so maybe of some use....

T. Blom
Information analyst
tbl@shimano-eu.com