I am trying to use the DTS Designer in Enterprise Manager but man, oh man,
is it SLOW. At first I thought it might be my connection to SQL Server, but
I am watching a trace on the server and there is ZERO activity when these
DTS designer slow downs occur.

For example I have a DTS package that imports a bunch of data from an Excel
spreadsheet. The package has a "ExecuteSQL Task" to clear some tables.
That is workflow linked to an Excel Spreadsheet connection which has a
series of six transforms linked to a SQL connection. If I double click on
one of these transforms it takes 28 seconds for the Transform Data Task
Properties dialog box to appear. 28 seconds and ZERO activity on the SQL
Server. That's just the tip of the iceberg. Clicking on the
"Transformations" tab of the dialog box takes 10 seconds for the transform
tab to actually appear.

I had worked with DTS quite a bit three years ago and remembered it being
slow like this then. But here we are three years latter, faster laptop,
newer version of Enterprise manager (version 8.0) etc. but it's still slow.

Anyone else suffering with this? Any fixes?

Thanks,

Gary Shell

Are you running the latest SP ?
What OS?
If the source system has a large amount of tables and views then it is a
known issue that DTS must enumerate the whole lot causing slow down.

Have a look at Network traffic.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Gary Shell" <gshell (AT) fuse (DOT) net> wrote

Latest SP on both server and laptop. Running XP on the laptop and Win2000
server.

I just found the reason for the slow down. I had the excel spreadsheet open
in excel!!! I was reviewing the actual data as I created the package. But
right after I posted my message here, I happened to open the package again
and didn't have the spreadsheet open. Voila', it opened up very quickly.
Hmmmmmm, I thought. I had a hunch, so I opened the spreadsheet and tried
opening the package again. 28 seconds to open. Closed the spreadsheet and
it takes about a second to open the DTS package.

<THUD> (Sound of heel of hand striking forehead)

Well, doh!!!!

Boy do I feel stupid. I have struggled with this for three days.
Arghhhhhhhhh!

BTW. I can leave Excel itself open as long as I close the spreadsheet in
question.

Thanks
Gary
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote