SQL Agent Job execute only if rows don't exist

  • 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?

  • 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

  • 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

  • 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.

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

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