Check table availability before running job

[Former Member]

We have a daily job that depends on the availability of certain data warehouse tables. Under normal circumstances, the data warehouse table refresh (a process external to my team) is complete before our job runs. On occasion, the DW refresh job runs long, and causes our job to fail when we try to access tables that do not exist.

How would I add a step to the beginning of my job that verifies availability of the required DW tables, and if they are there the job runs; if not, the job should wait x minutes, then try again (up to y times)?

[Former Member · Accepted solution]

Hello

Add a script that executes SQL to query the tables (or metadata tables) at the start of your job.  You can put the script into a while loop with the sleep function, and drop out after y attempts.

Simple!

Michael

[Former Member]

Thank you Michael. I ended up putting a Try/Catch block inside the While loop to fully meet the requirements, then a Conditional after the While loop to either continue or fail the job.