We have several instances of SQL Server 2000(sp3) on a Windows 2003
development box. (3gigs ram, dual P4 2.8ghz xeons).

We are experiencing a problem where DTS packages are taking an unusually
long time to open. This only occurs when using EM on other clients and not on
the actual local server. I have tried turning on CACHE but it doesn’t seem to
work. If you have any ideas or could at least explain how or why it’s slow
please send info this way. Incidentally, small and simple packages open up
much quicker than the large complex ones.

I realize complex/large DTS packages will take longer to open because
metadeta is being collected when a package is open, but these packages are
taking twice as long to open than they did when they were on a much slower
sql server.

What happens if you save the package to a structured storage file and do
it from there?

"softengine" <softengine (AT) discussions (DOT) microsoft.com> wrote

Well at first I thought it was how we transferred the packages from the old
SQL Server to the new and faster SQL Server. We used a free program called
DTSBackup 2000. But then I created a test package directly on the new SQL
server and I get the same problem.

When I take a package and try to do a Save As to either a Structured Storage
File or metadeta, it takes a long time to save and just as long to open.

"Allan Mitchell" wrote:

OK

Can you tell me what is in the package?

All packages do this or packages of complexity only?

SP level on SQL Server, MDAC?

Anything in the Event Log?

"softengine" <softengine (AT) discussions (DOT) microsoft.com> wrote

These are mostly ETL packages w/mostly data transformation tasks. The more
complex packages do seem to take longer but the simple ones still take twice
has long to open as they used.

More importantly. the packages open at the normal time through the
enterprise manager on the the local server. This is only an issue on remote
EM clients.
When I try to open up a large table through a remote EM, it opens up very
fast.

We are using SP3 8.00.760, and actually it is a Windows 2000 standard
edition not Windows 2003.

Is it possible this could be a network problem?

Anyone?

"Allan Mitchell" wrote:

It is certainly looking that way.

Network protocols between the boxes?

Switch problems/slow down?


"softengine" <softengine (AT) discussions (DOT) microsoft.com> wrote

Here is on other clue. The msdb.sysdtspackages takes forever, I mean at least
a minute to open from a remote Enterprise Manager, but opens quickly on the
local EM. Also all the msdb tables open up quickly in the remote EM.

Is it possible that some of the MSDB tables were corrupted when I used
DTBackup2000 to copy over the DTS packages?

Is there a way to refresh/repair the MSDB database?



"Allan Mitchell" wrote:

If they were corrupted you would not see the packages anywhere.

You see the packages fine they just take a long time.

Are there many versions of the package in MSDB?

What if you save this package to another server and opened?

Now try to open this package on the now remote server which was
previously the local server

This way if it opens quickly you can say

The package is fine
Opens quickly locally on any server
The only difference is the network in between.



"softengine" <softengine (AT) discussions (DOT) microsoft.com> wrote

It looks like the issue was resolved and it was related to the network card
and its auto-sensing 10mbs/100mbps function. We forced the NIC to always run
on 100mbps. I can't believe it takes that much more bandwith to open up an
DTS package.

Thanks for than help

"Allan Mitchell" wrote:

I have also seen this with the most innocent of things also (not DTS) as
soon as you told the NIC to use 100 mbps and not try to be clever
everything was fine.



"softengine" <softengine (AT) discussions (DOT) microsoft.com> wrote