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=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF
End Function
Thanks a lot.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|||sorry, i pressed the save button twice... and the same thing got posted twice... there sould be an option to delete a post....|||hi upalsen,
thanks a lot. it's now working. great!
god bless.|||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.
