Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: How to stop execution of DTS package (during loop)

    I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.

    ActiveX Script 1
    Option Explicit

    Function Main()

    Dim vDate, vCounter, vBranchCode, vPath

    vDate="011207"

    vCounter=DTSGlobalVariables("gVarCounter").Value

    IF vCounter<=3 THEN

    IF vCounter=1 THEN
    vBranchCode="ALB"
    vPath= "D:\PROJECTS\HRIS\ALB\"
    ELSEIF vCounter=2 THEN
    vBranchCode="MOA"
    vPath= "D:\PROJECTS\HRIS\MOA\"
    ELSEIF vCounter=3 THEN
    vBranchCode="PSQ"
    vPath= "D:\PROJECTS\HRIS\PSQ\"
    END IF

    DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"

    Main = DTSTaskExecResult_Success

    ELSE
    <This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
    DTSGlobalVariables("gVarCounter").Value=1
    <DTS Process should stop execution...how is this?>
    END IF

    End Function
    After excel to sql dts
    ActiveX Script2
    Function Main()

    IF gVarCounter<=3 then
    DTSGlobalVariables("gVarCounter").Value=DTSGlobalV ariables("gVarCounter").Value+1
    DTSGlobalVariables.Parent.Steps("DTSStep_DTSActive ScriptTask_1").ExecutionStatus=DTSStepExecStat_Wai ting
    Main = DTSTaskExecResult_Success
    END IF

    End Function
    Thanks a lot.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    there are many ways to stop a DTS. u can simply write

    Main = DTSTaskExecResult_Failure

    in the ELSE part and link the next step with "on success" workflow. or u can even write a blank ActiveX step and redirect the flow to that step in the ELSE part with "...DTSStepExecStat_Waiting" as u r already doing
    Last edited by upalsen; 01-16-07 at 03:24.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    sorry, i pressed the save button twice... and the same thing got posted twice.... there sould be an option to delete a post.....

  4. #4
    Join Date
    Oct 2005
    Posts
    119
    hi upalsen,

    thanks a lot. it's now working. great!
    god bless.

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    hi LimaCharlie,

    thanks for your acknowledgement. many here do not acknowledge the solution they have accepted. an acknowledgement is (1) a recognition for support (2) helps in closing the post (3) helps a third user who is viewing the post at a later date (or coming from search engine) to understand what the final solution could be.

Posting Permissions

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