August 5, 2004 at 2:01 am
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
August 5, 2004 at 7:54 am
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.
August 5, 2004 at 7:19 pm
Bruce, I'm not sure I understand your reply.
I have access to the package designer, it's how I created the package.
The package will be running on a schedule, I just want a clean way to break the look and reset it to the waiting state.
Regards,
Mark
August 6, 2004 at 6:27 am
Mark,
Check out this example. I think this will give you an idea of how to do what you want.
Kris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply