I have a DTS package, which after running daily with no problems for a couple of years, has started to play up. Originally it was inserting data into a table which would then fire an insert trigger. The trigger used [inserted] to update three columns in the table. Usually, it was updating about 500,000 rows of inserted data.
When the problem started, the update statement inside the trigger was taking hours to run and usually had to be cancelled. Disabling the trigger allowed the DTS to run as normal. Running the UPDATE statement in a regular query window didn't cause any problems - it ran in less than 10 seconds.
To get around the problem, another task was added to the DTS to run the UPDATE after the INSERT in place of the trigger. This worked OK for a while, but the separate UPDATE task has now started to exhibit the same problem that the trigger had. The UPDATE still runs as expected if done in a regular query window.
The DTS package was created in SQL 2000 and is running on SQL 2005 Enterprise x64.
I'm at a bit of a loss to try and figure this one out. Any ideas?
Have you tried looking at the query plan for the UPDATE statement? You can do that in query analyzer/ssms.
What indexes does the table have on it? Maybe they are having to be maintained and are slowing the update down.
Can you go into detail on the UPDATE? Is it one SQL statement or are you using a CURSOR?
Converting the whole thing to SSIS would most likely add performance, but that depends on what you're actually doing in DTS.