Creating a schedule with a file dependency

So last post I talked about how to create schedules where they were dependent upon other jobs being completed first.

In this post I will show you how to make you job dependent on a file. This is really useful where your ETL needs to wait for an external process to complete first before it can run.

To do this I created a function called WaitForFileDependency which you can take a look at below:

#First check if the overide parameter has been set. If it hasn't been set then assume it should be false.

$P_Override = nvl($P_Override,'FALSE');

#Print some details about the file we are looking for in the trace file
print('File Exists: ' || file_exists($P_PreRequisiteFile));
print('File Create Date: ' || get_file_create_date( $P_PreRequisiteFile , '1900.01.01 00:00:0000'));
print('Last Job Run Date: ' || $P_LastRunDate) ;
print('Time Out Time: ' || $P_TimeOut);

#If the override variable was false, then start checking to see if the file is present.
if ($P_Override = 'FALSE')
begin
print('Dependency file ' || $P_PreRequisiteFile );
                while (
(
                                #If the file is not yet present
(file_exists($P_PreRequisiteFile) = 0) 
   or 
                                #Or the file is present but its from a previous run and so is an old file
(get_file_create_date( $P_PreRequisiteFile , '1900.01.01 00:00:0000') < $P_LastRunDate) 
 )
                and 
                                        #And we have not yet reached our timeout time
systime() < $P_TimeOut
)
begin
print('Waiting for file ' || $P_PreRequisiteFile || ' to arrive.');
                                #The the file hasn't arrive yet and you should wait a while and then check again.
sleep(300000);
end

               #If we reach the timeout time and the file has not appeared yet then fail the job
if (systime() > $P_TimeOut)
begin
print('Current Time: ' || systime() || '> Timeout time: ' || $P_TimeOut);
raise_exception( 'File not found before timout reached. Job will now fail');
end

                #If the file exists, and it was created since the last time we ran the job, then you can start running the current job
if ((file_exists($P_PreRequisiteFile) = 1) 
   AND
(get_file_create_date( $P_PreRequisiteFile , '1900.01.01 00:00:0000') > $P_LastRunDate) )
begin
print('File ' || $P_PreRequisiteFile || ' found');
print ('Starting current job.');
end
end 
else 
begin
                #If the override variable was set to true, then just start running the job without waiting for the file to appear
print ('Dependency Override in effect. Will begin job immediately.');
end
Return 1;

The call to the function looks like this:

WaitForFileDependency($G_PreRequisiteFile,$G_Override_Dependency,$L_LastCompleteDate,to_date('1900.01.01' || $G_TimeOut,'YYYY.MM.DD HH24:MI'));

• $G_PreRequisiteFile - This is the file, with its path, that you are waiting to appear
• $G_Override_Dependency - If its TRUE then start the job immediatly, but if its FALSE then wait for the file to appear
• $L_LastCompleteDate - This is the last time the job was executed
• $G_TimeOut - This is the time of day you want to stop looking for the file and fail the job. If you normally expect the file to arrive by 5:00am, and its still not present by 6:00pm, then you probably want to stop waiting for the file to arrive and alert people to this fact.