Post reply

Multiple precedence constraints pointing at a task

  • March 11, 2004 at 7:03 am

    #63389

    Hello All:

    SQL Server 2000 DTS assumes a logical "and" relationship between multiple precedence constraints pointing at a single task in the designer.    Does anyone know of a way to change this assumption, so that the task is executed if any one of the precedence constraints pointed at it is triggered?  I have a package with multiple steps, if it fails anywhere I want it to execute a step that sends out an email.  Do I need to create multiple copies of this send mail step and attach one via an onfailure constraint to each step in my package?  Or is there some way to use just one?  (Which I would much prefer)

    Thanks,

    Joe

  • March 12, 2004 at 11:08 am

    #498563

    No expert here but I know where you can find one.

    Check out http://www.sqldts.com/?246

    This is a really useful site with tons of DTS expertise overflowing.

    His example of looping uses a trick. He sets a specific step, in this case your "oops I need to send an email cause something went wrong" step as DTSStepExecStat_Waiting.

    Then you could have just previously set ActiveXScript to put into GlobalVariable what a DynamicProperties task populates your email message and subject and recipient.

    So the email task is last in line of chained tasks and it will say "congratulations your done" unless you fail at which point it will run next but say "oops something went wrong".

    So the DTSStepExecStat_Waiting will tell your package to do this email step next instead of last when ever something goes wrong. 

    Now you need error handling code to kick the process off if an error occurs.

    Hope helps some. 

     

  • March 12, 2004 at 11:52 am

    #498575

    Thanks for thet tip.  I was hoping to avoid ActiveX scripts, but this doesn't look too bad.  That site looks very useful as well.

Viewing 3 posts - 1 through 2 (of 2 total)

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