Conditional Flow in DTS

  • Wayne-153714

    Hall of Fame

    Points: 3536

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2929.asp


    When in doubt - test, test, test!

    Wayne

  • Michael Lysons

    SSCertifiable

    Points: 6466

    I'm still in shock from the fact that I've only just found out you can do this!

    Nice one - you've made my day

  • mrpolecat

    SSCertifiable

    Points: 6934

    I have always found that workflow scripts are hard to manage because you can't see them in the designer.  When you go to edit old packages that you have forgotten or someone elses code it is hard to figure out what is going on when steps don't fire because of the workflow script.  Instead I use activeX scripts to enable and disable future steps based on previous actions.  These are easier to maintain because youcan see the step in the designer.

    I use the following subs to set precedence and diable steps.

    sub setprec(dest,src)

    Set oPkg = DTSGlobalVariables.Parent

    Set Destination = oPkg.Steps (cstr(dest))

    Set Source = oPkg.Steps (cstr(src))

    Set Prec = Destination.PrecedenceConstraints.New(Source.name)

    Prec.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult

    Prec.value = DTSStepExecResult_Success

    Destination.PrecedenceConstraints.Add Prec

    Destination.DisableStep = False

    end sub

    sub chkfiles(flnm,tsk)

    set fso=createobject("scripting.filesystemobject")

    if fso.fileexists (flnm) then 

     DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_"&tsk).DisableStep = False

     setprec "DTSStep_DTSExecuteSQLTask_2","DTSStep_DTSDataPumpTask_"&tsk

    else

     DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_"&tsk).DisableStep = True

    end if

    end sub


  • Francisco Rodriguez-252375

    SSC Enthusiast

    Points: 162

    Hi, Wayne, good article.

    I have one question.

    I run a step that moves info from one table to another and if one or more records were copied I'd like to move to another step (just if records where copied) and if there was an error or no records were copied I'd like to move to a different step. I don't want to depend on success of the step because if there were no records to copy I would receive a success status.

    How can I do this ?

    Thanks in advance

  • Lawrence Rajan

    SSC Journeyman

    Points: 80

    Hi Francisco,

    To address your question, you can create another Execute SQL Task after the transformation task that does a COUNT(*) from the destination table and if records exist, proceed with the flow that Wayne explained in his article. Hope this helps.

     

  • Carlos Urbina

    SSCommitted

    Points: 1790

    Superlative article! worthy of a DTS for Dummies book.

    What I've seen before are MRPOLECAT type scripts which I'm too dumb to get to work.

    But your scripts really helped. I have a ton of places where I can use this.

  • Chada

    SSC Enthusiast

    Points: 185

     

    A very nice  article!

    Scripting is really helpful. I have a many a places to pen down where I can use this type of scripting.

  • shailendra-251422

    Ten Centuries

    Points: 1111

    This is excellent!!

    I had attempted using that option but could never figure out how it should be used. This will really help me. Thanks!

  • Ralph D. Wilson II

    Old Hand

    Points: 394

    At one time, I would have considered this to be a "fluff" article that was so obvious that it was a waste of time to read, much less to write.  However, I have recently discovered that, apparently, an awful lot of DBAs come from a non-programming background and that makes this kind of article very valueable. 

     

    Please, don't get me wrong, I am not making a negative comment . . . I am saying that your article:

    • Is valuable to the community;
    • Has helped me recognize that I may have an advantage due to both the quantity and nature of my experience;
    • reminds me that the "little things" that one sometimes assumes are "common knowledge" may actually be anything but "common knowledge."

     

  • alexx

    Old Hand

    Points: 320

    great work man...simply with great potential ..sorry my english...

  • Francisco Rodriguez-252375

    SSC Enthusiast

    Points: 162

    Hi, Lawrence.

    Thanks for the advice but this is exactly what I don't want to do.

    I'd like to see if in one step I can load the info and validate the number of rows transferred in order to move to a new step (depending in the number of rows).

    Best regards,

  • John Dempsey

    Hall of Fame

    Points: 3115

    I agree with you that it is not intuitive when you have a workflow property on a step.  This happened to me on a DTS package I was given to maintain and was a real pain to find out what was going on.  My solution once I found it was to just put an annotation note next to the step regarding the workflow property.  After that experience, I said to myself that I would probably not use them in the future.  But, after reading this article I may have to rethink that position.  A simple note about a workflow property would let you know to look into the step for the code.  Nice simple article.

     

  • Esalter

    SSC Veteran

    Points: 233

    Great - just what I was looking for however I found that the If statement had to all be keyed as a single line for it to work.

    Strange really as the MS SQL HELP also formatted the IF as per the example here.....

    Now all I need to be find, is how to enquire on a table using ActiveX...:Whistling:

    Trainee Novice:w00t:

Viewing 13 posts - 1 through 13 (of 13 total)

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