Re: Powerful server takes more than 30 minutes to run DTS package

On Oct 4, 8:54 am, Devious Devil
<DeviousDe...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,

Problem Context:
I have a group of related DTS packages each one produces a single CSV file.
I have another DTS package which calls each of the related group one after
another to genereate all the files in one go. The DTS packages are run via a
stored procedure that calls xp_cmdshell that runs DTSRun.exe. The SP is call
from a .NET 2.0 WinForms application. The proxy account has been set and is
used to run the DTS packages. A windows account for the proxy is used and has
access to the destination directory for the files.

For most of our clients it works fine however for 1 of our clients it
timesout. The timeout limit in .NET code for the export is 30 minutes.

Now for the strange bit, on our development server using a copy of the
clients database the export takes between 5 and 6 minutes (well within the 30
minute limit); our server is a 2 CPU, 2 GB RAM, Win 2003 SP1, SQL Server 2000
SP4 machine. The clients server is a 4 CPU, 4GB RAM, Win 2003 SP2, SQL Server
2000 SP4.

There doesn't seem to be anything running on the server that would impact
the SQL Server in any significant way, certainly nothing that would cause
their server to run at least 5 times slower than ours. In fact our server in
theory is under more pressure as developers from other projects also use it.

Anybody with any ideas that we could check or things we could try would be
much appreciated.

Regards,
Peter

Hi Peter,

Have you tried running SQL Profiler and Perfmon to see what it is
actually doing? It seems unlikely that it would be hitting any
resource constraints, more likely there is a permissions issue
somewhere in the chain of events.

Good Luck!
J

.

Relevant Pages

  • RE: Recover SQL files & DST packages from server backup
    ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ... >> Server Settings''. ... >> In order to backup and restore the DTS Packages, ...
    (microsoft.public.windows.server.sbs)
  • it all started on 5/27....
    ... individual DTS packages on SQL Server 2000 SP 4 that import and convert ... The SQL lives on a separate server than Pervasive. ... the same machine as SQL Server 2K SP 4. ... Now, if I execute the DTS packages individually on the server, all ...
    (microsoft.public.sqlserver.dts)
  • Re: Very Very Urgent, Please Help
    ... does your packages are stored in the MSDB database? ... Have you changed your main package to use the right SQL Server instance? ... When my main dts package calls other dts packages getting timeout immediately. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Very Critical, Please help
    ... Check with your Server team to make sure that nothing emanating from the SQL Server is being blocked from leaving the box. ... My Main dts package calls many other simple dts packages. ... >> Step Error Help Context ID:0 ... >> General network error. ...
    (microsoft.public.sqlserver.dts)
  • DTS periodic failures
    ... It runs 16 DTS packages scheduled to run once per minute via SQL Server ... server but each DTS package only accesses a single database. ...
    (microsoft.public.sqlserver.dts)