I've had a couple of DTS packages that run as scheduled jobs that worked fine for weeks after they were designed and deployed. They basically query a database and return data for a particular date range, outputting it in the form of text files for transmission to vendors.
The first step in each process is to query a process log table to determine the date range used for the last successful process of that type. It then updates the global date range variables in the DTS package to reflect what the next range should be and runs the data export process according to those new values.
These jobs run every week, and last week they both stopped working off of the derived date ranges and instead, now run on the default values provided for the global variables at design time. The kicker? The two DTS packages in question run on different servers. One runs exclusively against resources that reside on the server it is running on (we'll call it ServerA), while the other runs on a second server (ServerB) that does reference some data on ServerA.
I have spoken with the server admins, and no one is aware of any updates or patches (SQL Server or otherwise) that have been applied to either server that may have altered this behavior.
Any ideas where I might look next? I'm open to the possibility that it may be related to something that may have changed on my development workstation (almost all DTS work is done remotely), but I'm not sure what that could be. Any ideas?
EDIT: I should add that both of these DTS packages exhibit the same behavior when I manually run the packages, themselves, under my Windows credentials (local admin).