We've a monthly process where we receive input files from the source systems and create an extract to an external system - but the problem is that we dont know on which day of the month we would receive the input files.
Job that reads data from the input files and loads the Staging environment.
Job that loads data from the Staging to the extract tables - this job would load the extract tables ONLY when we have a token file ready ("Stage ready file") which signifies that the data in the Staging env is "clean".
Job that copies data from the extract tables into the external system. Needs to run only when there's data to be copied in the extract tables.
So the sequence would be
Job A --> Job B --> Job C
Please note that no scripting is allowed in Job C.
This is how I have planned it:
Job A would run everyday looking for the source files and would load the staging env. Job B would have a TIDAL dependency to kick off only when the token file ("Stage ready file") is available. Job C would have a TIDAL dependency on Job B to "Complete Normally".
1. Stage ready file would be available only once in a month, but all the jobs would be kicked off daily. Thus, Job B would be in the "Waiting for dependencies" status looking for the "Stage ready file" on Day 1 for a time window - beyond this window the job would time out. Do we have some pseudo script/sample indicative commands which can update the status of the TIDAL from "Timeout" to "Completed Normally"?
Do you have any other ideas to implement this scenario?