How to end a looped package cleanly

• Mark Hawkins

  SSC Veteran

  Points: 233

  More actions

  August 5, 2004 at 7:42 pm

  #66519

  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

• Mark Hawkins

  SSC Veteran

  Points: 233

  More actions

  August 8, 2004 at 6:09 pm

  #518112

  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