Error handling bug between DTS and SQL Agent

Error handling bug between DTS and SQL Agent

Post by Richard » Fri, 12 Apr 2002 23:49:33



I have noticed an irritating problem with the way that SQL Server Agent
handles errors generated by DTS packages. Is this a bug, or a bizarre design
feature. (Or have I missed something...)

I have a DTS package which runs a single stored procedure on a remote
machine. On occasions this sp fails (due to deadlock conflicts or other
issues, which are beyond my authority to change) The package is called as
part of an ETL process, controlled by SQL Server Agent. If the DTS package
fails, then the SQL Agent job step fails, and the rest of the job can't
proceed.  The solution is usually to wait a couple of minutes and re-run,
but this needs to happen automatically.

I therefore changed the DTS package to attempt to run the stored procedure,
then if it fails to wait 2 minutes then try again.
This is achieved by a simple "on failure" workflow link from the sp call to
an ActiveX  step that waits 2 minutes without doing anything, then an "on
success" workflow to another step which tries again to run the sp.
The DTS package works exactly as intended, and the second attempt is started
only if the first has failed. Looking at the package log, it reports that
the package executed successfully,and the detail shows that that  the one
step failed, which is correct.

If I run this from SQL Server Agent, the failure of the step causes the SQL
Agent job step to fail, even though the package as a whole has completed
successfully. This renders the whole exercise pointless. I have not got
"fail package on first error" set, nor have I got the workflow packages of
the failing step set to "fail package on step failure". Either of these
would fail the package straight away, and change the reported status in the
log.

There is an obvious work-round, using SQL agent to test for a failed step,
then call a wait step and try again, but it is not elegant, and shouldn't be
necessary.

Is this a known bug with SQL Agent?

Is there a way I can force the DTS package to return a code acceptable to
SQL Agent which will allow it to class the step as successful? I could put
the try/wait/try logic in the SQL job, but I'd rather not, as it's not
elegant.

Regards,

Richard R.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Bob Pfeiff [MS » Sun, 14 Apr 2002 23:09:02


Did you try changing the setting for "On failure action" in the job step to
"Go to the next step" instead of "Quit the job reporting failure"?

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Richard » Tue, 16 Apr 2002 16:42:14


I hadn't done that, since this means that for ANY failure of the DTS
package, the SQL job will continue.
What I want to do is to try the SQL task three times in the DTS package, and
if it fails three times, then fail the package and stop the job.



Quote:> Did you try changing the setting for "On failure action" in the job step
to
> "Go to the next step" instead of "Quit the job reporting failure"?

> --
> Bob
> Microsoft Consulting Services
> ------
> This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Bob Pfeiff [MS » Thu, 18 Apr 2002 11:59:11


Even though the package isn't set to fail on first error, the error is
raised back through the Job context.  This definitely interferes with
exception logic in the DTS package.  I'm going to ask someone else about
this.

--
Bob Pfeiff
Microsoft Consulting Services

This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Bob Pfeiff [MS » Fri, 19 Apr 2002 07:34:24


Is it possible to control your entire ETL process from DTS and only rely on
a Job to kick off the package?  That way it might be easier to manage
retrying tasks when errors occur.

Just a thought.

--
Bob Pfeiff
Microsoft Consulting Services

This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Richard » Fri, 19 Apr 2002 17:14:39


Bob,

There are 43 job steps, each of which calls at least one DTS package or
T-SQL stored procedure. I could write a DTS package which
called all the other DTS packages & T-SQL, and use the DTS error handling
that way. Unfortunately the system is now live, documented, and (almost)
finally invoiced, so it's really too late to make that sort of change.  It
will probably be easier to add in two job steps to catch the failure and
then re-try after a couple of minutes wait.

The reason I've used SQL-Agent as the overall controller is that it is
transparent what is going on, and in what order, which makes life easy for
the DBAs who have to support my systems once they're in operation. They are
generally familiar with SQL Agent jobs, but totally unfamiliar with DTS. If
a step does fail they can easily see which step it was, and (given a handy
troubleshooting guide), make a sensible decision as to whether the problem
is likely to be a SQL server problem or a weird untrapped data problem. They
then know whether or not they need to escalate the problem and call me to
look at it too, or  sort it themselves.  If the entire ETL is "hidden"
inside a DTS package, then it's more of a closed system that they can't see
inside.

Does your previous comment  "I'm definitely going to ask someone else about
this", suggest we might have actually found a bug in SQL Server, rather than
in my programming?? Well there's always a first time!

Regards,

Richard



Quote:> Is it possible to control your entire ETL process from DTS and only rely
on
> a Job to kick off the package?  That way it might be easier to manage
> retrying tasks when errors occur.

> Just a thought.

> --
> Bob Pfeiff
> Microsoft Consulting Services

> This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Bob Pfeiff [MS » Fri, 19 Apr 2002 22:14:30


I posted a description of your issue to an internal group where some of the
developers review the posts to see what they say.  I don't know if I'd call
this a bug, I'd guess it's working by design, but the way the two
controlling mechanisms (DTS and SQL Agent Jobs) work together obviously can
cause issues in implementing complicated processes like yours.

--
Bob Pfeiff
Microsoft Consulting Services

This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Kevin Connel » Sat, 20 Apr 2002 00:45:50


Normal behavior for SQL agent.

Richard told me not to harp on msft too much :-), but SQL agent is just a
stop-gap measure for desktop development -  get a real enterprise scheduler
for real enterprise scheduling.


Quote:> I have noticed an irritating problem with the way that SQL Server Agent
> handles errors generated by DTS packages. Is this a bug, or a bizarre
design
> feature. (Or have I missed something...)

> I have a DTS package which runs a single stored procedure on a remote
> machine. On occasions this sp fails (due to deadlock conflicts or other
> issues, which are beyond my authority to change) The package is called as
> part of an ETL process, controlled by SQL Server Agent. If the DTS package
> fails, then the SQL Agent job step fails, and the rest of the job can't
> proceed.  The solution is usually to wait a couple of minutes and re-run,
> but this needs to happen automatically.

> I therefore changed the DTS package to attempt to run the stored
procedure,
> then if it fails to wait 2 minutes then try again.
> This is achieved by a simple "on failure" workflow link from the sp call
to
> an ActiveX  step that waits 2 minutes without doing anything, then an "on
> success" workflow to another step which tries again to run the sp.
> The DTS package works exactly as intended, and the second attempt is
started
> only if the first has failed. Looking at the package log, it reports that
> the package executed successfully,and the detail shows that that  the one
> step failed, which is correct.

> If I run this from SQL Server Agent, the failure of the step causes the
SQL
> Agent job step to fail, even though the package as a whole has completed
> successfully. This renders the whole exercise pointless. I have not got
> "fail package on first error" set, nor have I got the workflow packages of
> the failing step set to "fail package on step failure". Either of these
> would fail the package straight away, and change the reported status in
the
> log.

> There is an obvious work-round, using SQL agent to test for a failed step,
> then call a wait step and try again, but it is not elegant, and shouldn't
be
> necessary.

> Is this a known bug with SQL Agent?

> Is there a way I can force the DTS package to return a code acceptable to
> SQL Agent which will allow it to class the step as successful? I could put
> the try/wait/try logic in the SQL job, but I'd rather not, as it's not
> elegant.

> Regards,

> Richard R.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Richard » Sat, 20 Apr 2002 03:51:21


Kevin,

I've used SQL Agent in lots of projects prior to this, inc enterprise ones.
What would you suggest as a SQL Agent replacement? Are they 3rd Party
options, or does MS make something that they don't shout about?

Regards,

Rich

-- Part of original message deleted to save space

Quote:> Richard told me not to harp on msft too much :-), but SQL agent is just a
> stop-gap measure for desktop development -  get a real enterprise
scheduler
> for real enterprise scheduling.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Kevin Connel » Wed, 24 Apr 2002 03:30:16


anything thats a general purpose centralized scheduler - CA Autosys,
tidalsoft sysadmiral, *mation, there are lots of them.  Even the MSX/TSX
paradigm doesn't come close to what a centralized scheduler can do - in
features or architecture.


Quote:> Kevin,

> I've used SQL Agent in lots of projects prior to this, inc enterprise
ones.
> What would you suggest as a SQL Agent replacement? Are they 3rd Party
> options, or does MS make something that they don't shout about?

> Regards,

> Rich

> -- Part of original message deleted to save space

> > Richard told me not to harp on msft too much :-), but SQL agent is just
a
> > stop-gap measure for desktop development -  get a real enterprise
> scheduler
> > for real enterprise scheduling.

 
 
 

Error handling bug between DTS and SQL Agent

Post by Richard » Mon, 06 May 2002 05:01:06


Kevin,
Sorry I've not posted back to this group for a while. These 'customer'
things can really get in the way of development sometimes!

I actually got the thing working using SQL Agent Job scheduling, and set the
re-try attempts from there, which was the original suggestion!
So far so good, though there is something on that production box that locks
the file out, despite what the DBA says.
I'll look for your suggestions on the web, sounds like I could be missing
out on something good.

Thanks to all who contributed,

Richard


> anything thats a general purpose centralized scheduler - CA Autosys,
> tidalsoft sysadmiral, *mation, there are lots of them.  Even the
MSX/TSX
> paradigm doesn't come close to what a centralized scheduler can do - in
> features or architecture.



> > Kevin,

> > I've used SQL Agent in lots of projects prior to this, inc enterprise
> ones.
> > What would you suggest as a SQL Agent replacement? Are they 3rd Party
> > options, or does MS make something that they don't shout about?

> > Regards,

> > Rich

> > -- Part of original message deleted to save space

> > > Richard told me not to harp on msft too much :-), but SQL agent is
just
> a
> > > stop-gap measure for desktop development -  get a real enterprise
> > scheduler
> > > for real enterprise scheduling.

 
 
 

1. SQL Agent Job / Error Handling

Should I expect to be able to handle a "typical" error, such as primary
key violation, in a stored procedure so that a Job that runs that
procedure will not fail??

For example, I have the following procedure:

CREATE PROCEDURE aesp_ErrTest


AS





else


============

Note that I am "handling" / looking for error 2627, which is Primary
Key Violation.  If I call this procedure from ISQLW or from another
procedure, and pass in values that would cause error 2627, I would
still get a return code of 0 (despite the reporting of error 2627),
which is desired.  However, if I schedule a Job to run this procedure,
it reports the error and causes job failure.

I know that I can avoid a Primary Key Violation by checking for
existence before the insert.  But this would in essence cause double
work.  This simple kind of error handling in conjunction with Jobs is
not too much to ask for is it ??

Any insights you may have will be appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. ADO connections are closing very slow

3. Error handling in DTS Execute Sql Task

4. GV$... VIEWS

5. DTS error handling from flat file SQL 7

6. US-OH-Columbus Software Developer, Oracle, KSH, C/C++, HPUX (1293-2011)

7. Problem with DTS + SQL-Agent - Error: -2147352571

8. Query with the "CHANGETO" command

9. Raising/returning custom error message to SQL Agent from job-executed DTS pkg

10. SQL Server Agent Error when running a scheduled DTS package

11. sql 7.0 error 213, insert error, sql server agent, editting job step

12. Error handling under SQL 6.5 and SQL 7.0, @@error doesn't work