Post reply

DTS - fault tolerance

  • February 26, 2004 at 3:54 am

    #60060

    Hello - I have a number of exec SQL tasks in a DTS package. If one fails (i.e. tries to drop a table that doesn't exist) then the whole package fails. I haven't set fail on first error on the package properties. Also even if I set on completion workflow it still seems to bomb if it encounters an error whilst perfoming any task (SQL,activex,exec package). Any help appreciated -thanks

  • February 26, 2004 at 8:04 am

    #495863

    I can help with the example you gave of dropping a non existant table.  For each table you try to drop use this code:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[TableName]

    GO

    Then you won't get an error when a task tries to drop a table.

    Did you set all the workflows to on completion?  What kind of errors do you get?

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • February 26, 2004 at 11:31 am

    #495946

    I had problems with an activex script that wrote to a log file whilst another job still had a lock on it. It bombed out and the job failed even with 'on completion' workflow. However just spenty an hour trying to replicate with everything sent to on complete and it all does what it's supposed to!!! Also introduced the if table exists to all my drop table logic. Thanks for your help

  • February 26, 2004 at 1:17 pm

    #495992

    make sure to use the "on error" on your activex scripts too


    * Noel

  • February 26, 2004 at 7:21 pm

    #496073

    In my experience it is best to avoid the "on completion" constraint. It is much better and safer to setup your tasks to avoid failures (as illustrated by rmarda) and use the "on success" and "on failure" constraints to provide the workflow.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 27, 2004 at 7:10 am

    #496190

    When I've used 'on completion' I've had some 'unexpected' results so I'll try and stick with on success where posssible.

    Had a read around 'on error' and definitely looks like something I should be using - thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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