Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    14

    Angry Unanswered: DTS package - Loop?

    Is there a way to have a loop in a DTS package? Or at least to create a start and an end points? I have this process that I put in a DTS package and the process needs to be executed 13 times (involving the creation of different files based on a list of customers) and everything seems to work when I run it once but if I try to insert an "on success" arrow from the last process back to the begining it starts picking a different start point when I rerun it Does this make sense?
    Any help would be greatly appreciated
    --mike

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS package - Loop?

    I'm not positive that this will work, but you can try it:

    • 1. Add a global variable (package properties) called LoopCounter
      2. Add an ActiveX script (call it "LoopChecker") BEFORE the DTS task and use it to check the value of LoopCounter. If greater than 13, then fail the task; if less than 13 then succeed the task.
      3. Link this new ActiveX script to your existing DTS work path using the Success workflow.
      4. Add a second ActiveX script to serve as the terminator for the failed "LoopChecker" task
      5. Add a third ActiveX script (called "Loop Increment") to increment the LoopCounter variable AFTER the existing DTS task. Set it's workflow to point to "LoopChecker" on success (or even on completion).


    You can probably figure out a way to eliminate one of the ActiveX scripts and combine features.

    Best of luck, let us know what works.

    Regards,

    Hugh Scott


    Originally posted by Mickael
    Is there a way to have a loop in a DTS package? Or at least to create a start and an end points? I have this process that I put in a DTS package and the process needs to be executed 13 times (involving the creation of different files based on a list of customers) and everything seems to work when I run it once but if I try to insert an "on success" arrow from the last process back to the begining it starts picking a different start point when I rerun it Does this make sense?
    Any help would be greatly appreciated
    --mike

  3. #3
    Join Date
    Feb 2003
    Posts
    14

    Cool Re: DTS package - Loop?

    Thanks for the help. I actually found a solution by using the following activex script

    IF DTSGlobalVariables("Counter").Value <= DTSGlobalVariables("MaxCount").Value THEN
    Set oPkg = DTSGlobalVariables.Parent
    oPkg.Steps("DTSStep_DTSDataPumpTask_1").ExecutionS tatus = DTSStepExecStat_Waiting
    Main = DTSStepScriptResult_DontExecuteTask
    ELSE
    Main = DTSStepScriptResult_ExecuteTask
    END IF

    so if the 'IF' statement is true then it reset the first step of my process to wait status and therefore starts again

    Thanks again,
    Mike

  4. #4
    Join Date
    Mar 2009
    Posts
    1
    Hi,
    I know this is a very old thread but yet i found it to be very useful except for the fact that the code was not working for me. So i made a minor change which did the trick so thought of posting it here.

    Function Main()
    dim oPkg
    IF DTSGlobalVariables("@StartCounter").Value <= DTSGlobalVariables("@MaxCounter").Value THEN
    Set oPkg = DTSGlobalVariables.Parent
    ' The point in the DTS where you need the loop.
    oPkg.Steps("DTSStep_DTSDataPumpTask_2").ExecutionS tatus = DTSStepExecStat_Waiting
    END IF
    Main = DTSTaskExecResult_Success
    End Function

    Also the following site is quite helpful.
    Looping, Importing and Archiving

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •