I have some DTS packages that are failing occasionally. They have some pretty comprehensive logging in the various VBScript components and both SQL and text logging are enabled in each package. The logging works fine if I run the DTS packages from the server they live on.
In practice these packages are run using DTSRun from a remote machine that only has the SQL Server Client Tools installed. As DTS runs locally, where do the logs go in this case, if they are created at all? They're not on the server, there's no database on the client to do any SQL logging to and none of the text files were created on the client or server. Is it possible to debug DTS packages run this way?
Such as it is, the answer was to ensure the full path of the text logging is set up so it's valid on the machine where DTSRun is being called. In this case I had it set to C:\DTSErrors - this folder existed on the server, but not on the client. Creating C:\DTSErrors on the client solved my problem - now text logs are being created.
I assume the SQL logging just fails silently as there's no SQL server on the client machine to log to.