How to end a looped package cleanly

  • 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

  • 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