SQL Agent Job execute only if rows don't exist

Question

SQL Agent Job execute only if rows don't exist

joepacelli

SSCrazy

Points: 2088
More actions
April 4, 2016 at 7:28 am

#325424

I've got a SQL Agent Job that has 3 steps which call 3 SSIS packages
Step 1 - The first package extracts data from SharePoint
Step 2 - The second package extracts data from Oracle
Step 3 -The third package builds Data tables for the previous days data.
Most times everything works fine. But on occasion Step 2 fails with
Code: 0x000002C0
Source: ETL Connection manager "OracleConn"
Description: OCI error encountered. ORA-12170: TNS:Connect timeout occurred
If I re-run the job, it works fine.
But this requires manual process.
I know that step 3 populates a table called DIM.CoreData
Currently the job runs just past midnight
so I want to change the job to execute every hour
And add a step before step 1 to
SELECT COUNT(*) FROM DIM.CoreData WHERE CoreDate = CAST(DATEADD("d",-1,GETDATE()) as date)
If this is 0 then I either know it didn't run yet or it could have failed and to continue to Step 2
If it's not 0 then I want to exit the job
How can I achieve this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You could put in a new step one and use the On success and On failure actions of the new step to govern what the job does next. That requires that you force a step failure for one of the outcomes, which you may not be comfortable with. I think what I would do is add the logic to the SSIS package. Is that an option for you?

John

joepacelli SSCrazy Points: 2088 More actions · Answer 2

Yes, it is. I was considering this a last resort.

But it seems like the easiest.

I can add to every package.

Because I know we will soon remove the SharePoint one since this is only temporary

homebrew01 SSC Guru Points: 55544 More actions · Answer 3

Not quite sure if this addresses your problem, but .....

How about setting the step to automatically retry X number of times if it fails ?

I find that option useful for job steps that can be safely restarted.

Under the "Advanced" tab for the step, set # of retries, and interval between retries.

eg: Retry up to 5 times, waiting 2 minutes between each retry.