we have an agent job that runs a control dts package containing several child dts packages. The agent job reports failure every day but the logs for all the dts packages all report success. Has anyone come across this before?
asked
SQL Agent Job Reports Failures but Job Actually Succeeds
Check the steps. It's possible to have it successfully complete AND report a failure. Check the Advanced page of the Job Step Property. See if the "On success action" is set to "Quit the job reporting failure"
It's also possible you have multiple steps in the job and some are passing and some are failing. The entire job would report as failure (by default, although you can get around that too).
I've had the same problem with one weekly job. Since the company was shutting down in a few weeks I did not bother to try and find the cause of the problem. It was a job I was monitoring closely and I did not need the job status to tell me if it completed successfully or not.
I suspected that the failure might have been an issue with the job notification, probably caused by attempting to notify a user whose email account no longer existed. I guess I'll never know if that was it or not. Last week was the last week of my contract.
If I remember rightly, when I used to run scheduled DTS packages, some output messages from the package used to be interpreted as 'errors' by the Agent, even if the messages where just informational. After you've checked the completion flow logic as Grant suggests, I would be tempted to review the DTS output.
