August 5, 2004 at 7:42 pm
I've created a DTS package which does some simple data transformations, writes 2 .csv files then joins them.
I need one file per record and they need to be in a specific order, so I've looped the package so that it loops after setting a flag on the existing record.
The query that initiates it is:
SELECT RTRIM(MIN(SOP10100.SOPNUMBE))
FROM TR.dbo.SOP10100 SOP10100 INNER JOIN TR.dbo.RM00101 RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE SOP10100.SOPTYPE = 3 AND SOP10100.TIMESPRT = 0 AND RM00101.CPRCSTNM = 'BUNH.O1'
This give me one unprocessed record which I output to a variable which drives the rest of the process
It all works fine except that I want it to stop here when there are no more records that meet the criteria.
I've tried IF EXISTS, but because I use the MIN function, I get a NULL record and IF EXISTS doesn't trigger. So I wrote a little Activex task with examines the record and returns failure if the record is null.
Function Main()
Dim Doc
Doc = DTSGlobalVariables("Document").Value
If Doc <> "" then Main = DTSTaskExecResult_Success Else Main = DTSTaskExecResult_Failure
End Function
This works except that the task returns as failed so that I can use a on failure branch, but I really just want to terminate the package if the criteria is met.
Is there any way to just terminate the package successfully without it erroring?
Thanks,
Mark
August 8, 2004 at 6:09 pm
Did a little further diggin on this site and http://www.sqldts.com and came up with a solution:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim Doc
Dim LoopCheck
Set LoopCheck = DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1")
Doc = DTSGlobalVariables("Document").Value
If Doc <> "" then
LoopCheck.DisableStep= False
Else
LoopCheck.DisableStep= True
End if
Main = DTSTaskExecResult_Success
End Function
Since Disabling the step stops the on completion workflow precedent the loop stops cleanly.
Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply