Good day.I have dts running every 10 minutes in a server of slqserver 2000 who is in a country and that it sends data to another country which has a data base sybase12.5.The problem is when by reasons for the WAN the connection fails from a country to another one the dts or job fails executing its processes and I must soon give click to stop and start so that it continues. That I can do so that when the connection is lost and i need to dts reconected automatically and is continued its processing?
thanks and sorry my english
The best answer that I've found for problems like this is to establish a "watchdog" job. The watchdog runs very frequently (possibly every minute) and checks for evidence of the main job being interrupted... If the watchdog finds that evidence, then the watchdog restarts the main job.
In your example, I would suggest that you create a simple table to maintain both a log of events and to allow detection of the main job failing. Add a step before the main job to insert a row showing the DATETIME that it started. Add a step after the main job completes to show that it ended. This effectively logs the activities of the main job.
Create a whole new watchdog job that logs its beginning and end exactly the same way as you just added to the main job, then checks to be sure that the last entry from the main job is either a "success" or a start that could still be reasonably running (in other words, that the end for this job isn't overdue yet). If the main job needs to be restarted, let the watchdog job start it, otherwise let the watchdog end gracefully.
You should periodically check the log table to see when the watchdog runs, and how often it needs to restart the main job. This can be useful information, and will often warn you of trouble before the trouble becomes critical.