August 31, 2012 at 8:01 am
I have an intermittent failure with SQL jobs, it does not occur every night and not always to the same job or time where it appears the job does not run its DTS call. In the history it will say it is successful but only Step 1 appears and the DTS tasks / transforms are not logged as having been run.
I was thinking that the SQL agent account was having its permissions altered at some stage overnight, but I am assured this is not the case.
Also possible is that a network path in the DTS becomes unavailable. I have tested this and cannot replicate.
Basically, what I am after is any suggestions as to the problem.
Executed as user: SQL-ABC\abcdef. DTSRun: Loading... DTSRun: Executing... DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.
Some of the jobs do run under sa as well though.
August 31, 2012 at 9:44 am
Is this really DTS or is it actually SSIS?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 9:45 am
Oops, never mind. I see that DTSrun is definitely DTS (I was getting it confused with DTExec).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 9:50 am
How many steps does the Job have? What are the step types and what are the step commands?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 9:54 am
Also, make sure that you have one of the Log To Table/File options set in the Advanced tab of the Step properties. Then you should be able to check that Log for any step output that doesn't make it into the Job History.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 9:55 am
Thanks for your speedy reply.
There is only one step beyond that of starting the job.
Just a single call to DTSRUN.
I have reset the time schedule and waited to see if the same result occurs and have not been able to replicate. This is why I am thinking that there must be some sort of permissions glitch either to the network or to the SQL server.
August 31, 2012 at 10:11 am
rich-521822 (8/31/2012)
Thanks for your speedy reply.There is only one step beyond that of starting the job.
Just a single call to DTSRUN.
I have reset the time schedule and waited to see if the same result occurs and have not been able to replicate. This is why I am thinking that there must be some sort of permissions glitch either to the network or to the SQL server.
Do you have the Advanced Logging option turned on?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2015 at 10:47 pm
Grasshopper,
Did you ever find a resolution to this issue?
November 5, 2015 at 7:30 am
I have had several DTS job failures at various sites.
They normally fall into the following categories,
The SQL agent does not have write permissions to a drive, path or file.
There is a policy that prevents activex scripts from running.
There has been a change of IP address somewhere.
In this case, it was a corrupt transform (as a long shot I tried it as it was a known bug on 2000). I redid the transforms and it worked happily.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply