July 19, 2007 at 5:13 am
Hi,
We have to pull in data from 40 remote servers everynight.
We don't have permissions to use a distributed transaction, so I have gone down the DTS route.
I have built the DTS package so it only has one source connection and I loop through the entries in a table to change the connection properties as the package loops around the servers.
The problem is that sometimes the transformation seems to hang.
We set the process running, it will get so far around the remote servers, gets to about 200,000 rows tranformed on one server and just seems to get suck. We can leave it running for more than an hour and it still doesn't get any further.
So the question is:
is there anyway to pick this up in the DTS package, I was thinking some kind of timeout property but I can't find.
We sometimes know if a server isn't responding before we kick the process, in which case I just set a flag on the lookup table and the loop bypasses it.
I think the remote server is dropping it's connection during the import, but SQL Server/ DTS isn't picking this up and just waiting.
Any ideas?
Many thanks,
Rodney
July 19, 2007 at 5:18 am
I don't think so you can set timeout at DTS level you have to set it @ the server level.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 19, 2007 at 5:41 am
Thanks Sugesh for the quick reply.
Any ideas what else we could do?
I did wonder if I could use some kind of timer and check the number of rows transformed. If it's not increased from last time skip the transformation and move on to the next next server. But not sure if this is possible in a DTS package.
I did think about wrapping it all up in a .Net program but can you check how many rows have so far been transformed? I've worked out how to check how many were transformed once the data pump had finished...
Cheers,
Rodney.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply