notify if extract from source retrieved no data

[tfoster]

folks,

I have developed an etl to extract some data each day but there are some days when there will be no data to extract.  The users have asked if they can be notified on those days that there was no data.  The only way I can think of to do so is to check the log file afterwards to see how many rows were acquired and then send an email if there were zero rows to extract.  Does anyone know of a better way to do this?

thanks

[tfoster]

Folks,

thought I would post the method I found to notify users if no rows were extracted.  My jobstream is being run by a dos batch script and we use blat to send emails.  We currently do not use alert nodes to send emails.  What I did was add a prodedure node to by jobstream just after the build I wanted to check.  The procedure node does the following

1) gets the audit id of the previous build using the function NodeAuditId(id);
2) uses the function RowsInserted(audit id)  and checks if there were 0 rows inserted
3) if there were 0 rows inserted it uses the FileOpen, filewrite, and fileclose functions to create a text file
4) the dos batch script checks for the existence of this file once the jobstream has completed
    and if it exists then sends an email to the users letting them knos there were no records extracted

code is as follows

$audid := NodeAuditId('3');
if ( RowsInserted($audid) = 0) then
    $fileno := FileOpen('E:\I24REC\JOBS\JOB_LOGS\norecs.txt', 'WRITE');
    FileWrite($fileno,'no records');
    FileWrite($fileno,$audid);
    FileClose($fileno);


not sure if the above is the best/easiest way to do what I wanted but it does appear to work.   

[MFGF]

An alternative might be to code a procedure node to use the Lookup() function to count the number of rows in the source table (based on your filter conditions which determine what you collect).  If this returns a zero, then don't bother to run the build, and send an email instead, alternatively run the build and don't send the email.

Just a thought.

MF.