Hello!
In a SQL Server 2000 environment we have two servers where a DTS package is
moving data from one table on one server to the other (both SQL server 2000)
as a part of a datawarehouse(DWH) update process. This DTS package is one of
several updating the DWH from this server. The problem is that this one
particular DTS package acts very unstable. Running it manually it will always
update the table in DWH correctly, but in scheduled mode as a part of other
DTS packages that is updating the DWH it is acting unstable. All logs shows
that it has been executed as planned, there is no error message. It shows the
number or records transformed in the Transform Data Task, but when checking
the table it was supposed to update it will show that no records has been
copied to the DWH. One of the issues is that this doesn’t happened
perpetually, on the next scheduled job run it may work as intended and copy
all records as it should do. Logging the activity in SQL Profile shows that
it is simply running a “Select * from [table]” statement. It is no scripts in
the transform task on the DWH side, it should simply just copy the data.

I have double checked the permissions of the owner of the job, and it should
be all good since all the other DTS packages run with the same security
settings and is working as intended.

Does anyone have any ideas what to do next in order to solve this issue?