SQL Server Performance Forum – Threads Archive

Problem with DTS – occasional corrupted data

Hi, I have a DTS job that runs every 20 minutes. It connects to four remote SQL servers, gathers data and inserts it to a local database. two or three times a week, I encouter strange problems with the data transfer. The problems are very diverse, and appear quite random. Exmaples: 1. Rows that should have been transfered are simply not.
2. Rows are transferred, but certain column data ends up at the wrong column. Imagine you have two columns that contain an int on the remote DB table and the local DB table, they change places on the transfer.
3. Rows are transferred, but the job doesn’t update a value in the remote table that indicates that they were transfered, so they are again transferred 20 minutes later. All four remote databases are identical, and all four cause occasional problems. I shuld mention that 99% of the time there are no problems at all. What steps would you suggest in trying to solve this issue? Could a low quality/low bandwidth connection cause this problem? Bad configuration of the remote servers? Bad DTS design?

---

Check thru dts package log for information about steps executed in packages
Try to schedule each server’s job seperately rather all at a time.
Ensure there are no issues on the network during this activity. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Having a distributed transaction started before the start of the process might also help. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

]]>