How do end a loop in DTS?

Question

Post reply

How do end a loop in DTS?

Mark Hawkins

SSC Veteran

Points: 233
More actions
August 5, 2004 at 2:01 am

#88368

I've created a DTS package which reads records from 2 tables and writes .csv files.

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'

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 and gives errors instead of just finishing because no records are available.

Is there any way to just terminate the package successfully?

Thanks,

Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bruce Gilpin Mr or Mrs. 500 Points: 558 More actions · Answer 1

If you have access to SQL Administrator, use the package designer. It contains a text file icon which you can use for this purpose. If you don't find someone who does and ask them to do it for you. It should only take a couple of minutes. If your really ineterested, you can save the package as a text file and see all the VBA code. One look at that will convince you to use sql administrator for all your dts packages.