Long running DTS package

Hello,
I have a DTS package that took 52 hours to run. Within the package is a
task that runs a sproc which is a procedure to calculate premiums recvd and
has to run through over 2 million records. The procedure uses a cursor to
move through the records. My question is - is it possible that there were
memory leaks in the server while the package ran (there are a lot of other
things that go on on that server) making some of the data incorrect and some
of it correct? Because, I can take a portion of the data (a months worth)
and run that same process and it works just fine. Any ideas would help
tremendously.
Thanks,
PatriceMy goodness, that is a long-running process. I've had DTS packages going
through millions of records in a matter of 2-3 hours on an older machine.
There was some cursor activity in those processes as well. My guess is that
with some thoughtful redesign you can eliminate the cursor on a bunch of it.
(Can you precalculate some information and then handle the rest with joins
and aggregate functions?) That will make it easier to debug, as well as
run faster.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:1EC80DAD-CC19-447F-B9B5-A344A5E417E5@.microsoft.com...
> Hello,
> I have a DTS package that took 52 hours to run. Within the package is a
> task that runs a sproc which is a procedure to calculate premiums recvd
> and
> has to run through over 2 million records. The procedure uses a cursor to
> move through the records. My question is - is it possible that there were
> memory leaks in the server while the package ran (there are a lot of other
> things that go on on that server) making some of the data incorrect and
> some
> of it correct? Because, I can take a portion of the data (a months worth)
> and run that same process and it works just fine. Any ideas would help
> tremendously.
>
> Thanks,
> Patrice
>|||On Thu, 29 Dec 2005 07:28:02 -0800, "Patrice"
<Patrice@.discussions.microsoft.com> wrote:
>Hello,
>I have a DTS package that took 52 hours to run. Within the package is a
>task that runs a sproc which is a procedure to calculate premiums recvd and
>has to run through over 2 million records. The procedure uses a cursor to
>move through the records. My question is - is it possible that there were
>memory leaks in the server while the package ran (there are a lot of other
>things that go on on that server) making some of the data incorrect and som
e
>of it correct? Because, I can take a portion of the data (a months worth)
>and run that same process and it works just fine. Any ideas would help
>tremendously.
Can you post the cursor code?
Please look into the forward_only and fast_forward options. When you
have a (default) dynamic cursor, it can scale very poorly.
Josh