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

Logon to DTS from a different domain

I have a series of DTS's that need to be executed in a certain order and therefore have created a VB app that would execute the DTS's and perform a few administrative funtions for the user if successful.

The DBA for the SQL Server has given me a certain Windows Logon and I must logon via Terminal Server when I need to gain access to the SQL Server Manager. This is where I have created the DTS's - all created and owned by that user logon with certain rights. My VB application will be run from a different user machine on a different domain and therefore has a different logon- normal Windows logon. This user does not have access to the SQL Server and the DTS's need to logon with the same logon as under which it was created.

BUT...
LoadFromSQLServer offers only a SQL or Trusted connection by which one can let the user connect. By using the Windows connection flag, the LoadFromSQLServer function now ignores the logon username & password and posts the normal Windows logon. Obviously, the logon now fails for the user.

How can I let the LoadFromSQLServer funtion logon to the DTS Package with a different domain, username & password than the local Windows Logon information ?

GrahamAnybody out there ?

Login problem when scheduling a dts package

Hi.
(Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
As me, some of you might use the "Scedule Package" option on dts packages to
let the SQL server generate a job, and then copy the "Operating System
Command" string into another job which bundles together a set of dts
packages in one scheduled job.
My problem now is when I rightclick a DTS package and select "Schedule
Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user ' '. It got no detailed error number as the one you get on the
error: "...not assosiated with a trusted SQL connection..." stuff. It only
gives the general SQLState: 28000 error.
We have done a lot off stuff on our domain lately (converted to AD), but it
worked allright yesterday, but after a reboot this morning I got the above
issue.
Where should I start looking? Where do I set the login account which is used
when generating a job from a DTS Package? The Server Agent runs normaly, and
I'm logged into the server with the same ID which have created and owns the
DTS package
Please give me a resource URL or a hint on the above, if you have any
expiriences...
Regards
Mr. SmithThe problem with Authentication, can you provide for the DTS package, to
Windows authentication mode. so that the package run successfully. i faced
same problem.some R&D i fix that issue. can you proceed with windows
authentication.
still u have problem let me know.
"Mr. Smith" wrote:
> Hi.
> (Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
> As me, some of you might use the "Scedule Package" option on dts packages to
> let the SQL server generate a job, and then copy the "Operating System
> Command" string into another job which bundles together a set of dts
> packages in one scheduled job.
> My problem now is when I rightclick a DTS package and select "Schedule
> Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
> for user ' '. It got no detailed error number as the one you get on the
> error: "...not assosiated with a trusted SQL connection..." stuff. It only
> gives the general SQLState: 28000 error.
> We have done a lot off stuff on our domain lately (converted to AD), but it
> worked allright yesterday, but after a reboot this morning I got the above
> issue.
> Where should I start looking? Where do I set the login account which is used
> when generating a job from a DTS Package? The Server Agent runs normaly, and
> I'm logged into the server with the same ID which have created and owns the
> DTS package
> Please give me a resource URL or a hint on the above, if you have any
> expiriences...
> Regards
> Mr. Smith
>
>

Login problem when scheduling a dts package

Hi.
(Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
As me, some of you might use the "Scedule Package" option on dts packages to
let the SQL server generate a job, and then copy the "Operating System
Command" string into another job which bundles together a set of dts
packages in one scheduled job.
My problem now is when I rightclick a DTS package and select "Schedule
Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user ' '. It got no detailed error number as the one you get on the
error: "...not assosiated with a trusted SQL connection..." stuff. It only
gives the general SQLState: 28000 error.
We have done a lot off stuff on our domain lately (converted to AD), but it
worked allright yesterday, but after a reboot this morning I got the above
issue.
Where should I start looking? Where do I set the login account which is used
when generating a job from a DTS Package? The Server Agent runs normaly, and
I'm logged into the server with the same ID which have created and owns the
DTS package
Please give me a resource URL or a hint on the above, if you have any
expiriences...
Regards
Mr. Smith
The problem with Authentication, can you provide for the DTS package, to
Windows authentication mode. so that the package run successfully. i faced
same problem.some R&D i fix that issue. can you proceed with windows
authentication.
still u have problem let me know.
"Mr. Smith" wrote:

> Hi.
> (Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
> As me, some of you might use the "Scedule Package" option on dts packages to
> let the SQL server generate a job, and then copy the "Operating System
> Command" string into another job which bundles together a set of dts
> packages in one scheduled job.
> My problem now is when I rightclick a DTS package and select "Schedule
> Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
> for user ' '. It got no detailed error number as the one you get on the
> error: "...not assosiated with a trusted SQL connection..." stuff. It only
> gives the general SQLState: 28000 error.
> We have done a lot off stuff on our domain lately (converted to AD), but it
> worked allright yesterday, but after a reboot this morning I got the above
> issue.
> Where should I start looking? Where do I set the login account which is used
> when generating a job from a DTS Package? The Server Agent runs normaly, and
> I'm logged into the server with the same ID which have created and owns the
> DTS package
> Please give me a resource URL or a hint on the above, if you have any
> expiriences...
> Regards
> Mr. Smith
>
>

Login problem when scheduling a dts package

Hi.
(Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
As me, some of you might use the "Scedule Package" option on dts packages to
let the SQL server generate a job, and then copy the "Operating System
Command" string into another job which bundles together a set of dts
packages in one scheduled job.
My problem now is when I rightclick a DTS package and select "Schedule
Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed
for user ' '. It got no detailed error number as the one you get on the
error: "...not assosiated with a trusted SQL connection..." stuff. It only
gives the general SQLState: 28000 error.
We have done a lot off stuff on our domain lately (converted to AD), but it
worked allright yesterday, but after a reboot this morning I got the above
issue.
Where should I start looking? Where do I set the login account which is used
when generating a job from a DTS Package? The Server Agent runs normaly, and
I'm logged into the server with the same ID which have created and owns the
DTS package
Please give me a resource URL or a hint on the above, if you have any
expiriences...
Regards
Mr. SmithThe problem with Authentication, can you provide for the DTS package, to
Windows authentication mode. so that the package run successfully. i faced
same problem.some R&D i fix that issue. can you proceed with windows
authentication.
still u have problem let me know.
"Mr. Smith" wrote:

> Hi.
> (Environment: Win 2000 Server sp. 4. MS SQL 7.0 sp 4)
> As me, some of you might use the "Scedule Package" option on dts packages
to
> let the SQL server generate a job, and then copy the "Operating System
> Command" string into another job which bundles together a set of dts
> packages in one scheduled job.
> My problem now is when I rightclick a DTS package and select "Schedule
> Package" I get a [Microsoft][ODBC SQL Server Driver][SQL Serve
r]Login failed
> for user ' '. It got no detailed error number as the one you get on the
> error: "...not assosiated with a trusted SQL connection..." stuff. It onl
y
> gives the general SQLState: 28000 error.
> We have done a lot off stuff on our domain lately (converted to AD), but i
t
> worked allright yesterday, but after a reboot this morning I got the above
> issue.
> Where should I start looking? Where do I set the login account which is us
ed
> when generating a job from a DTS Package? The Server Agent runs normaly, a
nd
> I'm logged into the server with the same ID which have created and owns th
e
> DTS package
> Please give me a resource URL or a hint on the above, if you have any
> expiriences...
> Regards
> Mr. Smith
>
>