We have a DTS package that has four tasks, three connections, and two
global variables.

Global Variables: InstanceID and ProcessID, both integers.
InstanceID must be set on execution.

Connections: all are OLE DB for SQL Server using SQL Server
Authentication.
One is database R on server R
One is database O on server D
One is database A on server D.

Server D is a Windows 2003 Server running SQL Server 2000, SP 3a with
hotfixes (8.00.818) - RAISERROR calls do show up in DTS just fine (KB
815115).

The tasks:
1. Execute SQL task on database O, truncates a table "stage".
2. Execute SQL task that executes a stored proc on database A. This
takes InstanceID as an input parameter, and sets ProcessID as an output
parameter.
3. Data Pump task between databases R and O, pulls data from R into O
into the table "stage" (each record tagged with the ProcessID value
retrieved in step 2).
4. Execute SQL task that executes a stored proc on database O. This
stored proc takes as input parameters both InstanceID and ProcessID.
It opens a cursor on the table "stage", and for each record
executes one stored procedure on database O and a series of stored
procedures on database A. The result of these SPs populates two data
tables in database O and A.

Workflow:
1 and 2, on success, flow to 3.
3, on success, flows to 4.

The problem:
When executing this package from a remote client [tried from 4
different remote clients all running SQL 2000 Developer edition with
SP4], the package runs and completes. "stage" has 251 records in
it, O's data table has 251, and A's data table has 1500.

When executing this package from the SQL Server itself [remote desktop
connect to server D, or send a T-SQL statement to server D that uses
the xp_ procedure to run DTSRun], the package appears to run. Running
in Enterprise Manager, all four steps show up with the green
checkmarks, as if no errors occurred. The "stage" table has 251
records in it, indicating that the data pump task is working. Also,
running a Profiler trace shows that the SP call in step 2 is executing,
and there is a call being made to the SP in step 4 with both parameters
correctly set. However, although no error is recorded, nothing
happens. Both data tables are empty.

There is another user (local enterprise DBA) who had just the client
tools for SP3 installed. She was seeing slightly different behavior.
After she ran the package, the data table in O had 7 records, and the
data table in A had 42 - so the cursor did run, but only for a small
fraction of the records it should've. She then installed the SP4
update on her client tools, but it did not affect the results - still
7 records in O, 42 in A.

We have tried creating the package remotely, and creating it locally
(by using remote desktop to connect to D and create the package with
the Enterprise Manager installed there), and the results are the same.