November 10, 2004 at 9:46 am
Trying to use examples on sqldts.com but struggling.
Need to do the following:
1. check for a dummy file "complete.txt" and if not present exit package
2. Process files one at a time and depending on the left 18 characters of the file name, chose a path (different Transfer Data Task for each type of file)
3. When all files processed do some other processing.
I can get most of the above to work, except I can not fathom out how to loop back when I have multiple paths feeding into the end of the loop.
November 10, 2004 at 11:54 am
you can use activex to enable or disable steps causing DTS to loop or support better branch decision trees.
It requires you set global variables then evaluate them to turn a branch on or off
This turns on a main branch
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
If IsEmpty(DTSGlobalVariables("Global_Svrname").Value) then
'Disable and Enable Steps
DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSExecuteSQLTask_8")).DisableStep = False
DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSActiveScriptTask_1")).DisableStep = True
ELSE
DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSExecuteSQLTask_8")).DisableStep = True
DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSActiveScriptTask_1")).DisableStep = False
END IF
Main = DTSTaskExecResult_Success
End Function
This causes the loop back at the end of the main branch by setting one task to waiting to complete DTS will go back and start the loop again.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSExecuteSQLTask_7")).DisableStep = False
Dim oStep
set oStep = DTSGlobalVariables.Parent.Steps(cstr("DTSStep_DTSExecuteSQLTask_7"))
oStep.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
End Function
you can use the disconetted edit to get the step name.
Wes
November 11, 2004 at 8:13 am
Wes, many thanks. Lightbulb switches on. All the previous examples were much to clever for me...
For anyone who is having similar difficulties this is what I did.
After detecting the first file, I have an ActiveX Task to decide the path:
Function Main()
DIM oPKG
Set oPKG = DTSGlobalVariables.Parent
oPKG.Steps("DTSStep_DTSActiveScriptTask_5").DisableStep = True
oPKG.Steps("DTSStep_DTSActiveScriptTask_6").DisableStep = True
oPKG.Steps("DTSStep_DTSActiveScriptTask_7").DisableStep = True
oPKG.Steps("DTSStep_DTSActiveScriptTask_8").DisableStep = True
oPKG.Steps("DTSStep_DTSActiveScriptTask_9").DisableStep = True
SELECT CASE LEFT(DTSGlobalVariables("SourceFileName").Value,18)
CASE "complete.txt"
oPKG.Steps("DTSStep_DTSActiveScriptTask_5").DisableStep = False
CASE "pcatrcmgi00w800_01"
oPKG.Steps("DTSStep_DTSActiveScriptTask_6").DisableStep = False
CASE "pcatrcmgi00w800_02"
oPKG.Steps("DTSStep_DTSActiveScriptTask_7").DisableStep = False
CASE ""
oPKG.Steps("DTSStep_DTSActiveScriptTask_8").DisableStep = False
CASE ELSE
oPKG.Steps("DTSStep_DTSActiveScriptTask_9").DisableStep = False
END SELECT
Main = DTSTaskExecResult_Success
End Function
I added the same ActiveX Task at the end of Tasks 5 - through 7 to find the next file and loop back.
Steps 8 and 9 I don't want to loop.
Thanks again for your help
Stefan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply