April 4, 2016 at 7:28 am
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?
April 4, 2016 at 7:38 am
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
April 4, 2016 at 8:02 am
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
April 15, 2016 at 7:50 pm
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