DTS Takes forever to save

#1

I have a DTS Package that runs remote queries across a WAN and seems to take
FOREVER to save to the Database. Occasionally it will also report that
there is not enough storage to store the package into the database. The
package itself does not have too many steps in it. 1 connection object, 3
execute SQL tasks, 3 VB Script tasks and 3 Copy DB Objects steps.

Is there a way that I could more efficiently save this package while I am
testing it and making changes. Or can I tell what exactly is happening
during the save? I'm assuming that it's parsing and executing some of the
VB Script, and or the SQL tasks.

ANY help is appreciated.

Thanks
Scott

#2

Are you saving to SQL Server?

When you try to save to SQL Server is there any blocking going on?
Do you have a lot of packages (and versions) already in SQL Server?

I would look for the usual things when anything takes a long time
against a SQL Server as I do not see DTS being anything more than a
client.

What about when you save to a structured storage file?

Allan

"Scott M" <scott_M (AT) nospam (DOT) nospam> wrote

Quote:

I have a DTS Package that runs remote queries across a WAN and seems to
take
FOREVER to save to the Database. Occasionally it will also report that
there is not enough storage to store the package into the database. The
package itself does not have too many steps in it. 1 connection object,
3
execute SQL tasks, 3 VB Script tasks and 3 Copy DB Objects steps.

Is there a way that I could more efficiently save this package while I
am
testing it and making changes. Or can I tell what exactly is happening
during the save? I'm assuming that it's parsing and executing some of
the
VB Script, and or the SQL tasks.

ANY help is appreciated.

Thanks
Scott

#3

I have just always saved to the SQL Server. I will try the structured
storage file Monday and see if it saves faster.

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

Quote:

Are you saving to SQL Server?

When you try to save to SQL Server is there any blocking going on?
Do you have a lot of packages (and versions) already in SQL Server?

I would look for the usual things when anything takes a long time against
a SQL Server as I do not see DTS being anything more than a client.

What about when you save to a structured storage file?

Allan

"Scott M" <scott_M (AT) nospam (DOT) nospam> wrote in message
news:OLVFufwuFHA.3528 (AT) TK2MSFTNGP15 (DOT) phx.gbl:

I have a DTS Package that runs remote queries across a WAN and seems to
take
FOREVER to save to the Database. Occasionally it will also report that
there is not enough storage to store the package into the database. The
package itself does not have too many steps in it. 1 connection object,
3
execute SQL tasks, 3 VB Script tasks and 3 Copy DB Objects steps.

Is there a way that I could more efficiently save this package while I
am
testing it and making changes. Or can I tell what exactly is happening
during the save? I'm assuming that it's parsing and executing some of
the
VB Script, and or the SQL tasks.

ANY help is appreciated.

Thanks
Scott

#4

Yes, sorry. Still crazy from the hurricane down here.

Well, I saved it to a structured file and I got he same response time in the
save. I made a few changes in the DTS package (Deleted a step in the
workflow) and the package saves instantly, again.

I have no idea why this was happening! But everything seems to work again.
Thanks so much to everyone for their help.

Adios
Scott

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

Quote:

Did you ever try my suggestion of doing a Save As instead of a Save?
Can you "Save As" to a drive with space?

Allan

"Scott M" <scott_M (AT) nospam (DOT) nospam> wrote in message
news:exq#$j3wFHA.3864 (AT) TK2MSFTNGP12 (DOT) phx.gbl:

I'm trying to save the DTS and again it takes about 20 minutes for the
DTS
package to save.

Some of the queries in the DTS package take well over an hour to perform.
That's why I was hinting at the idea that SQL Server does something a
little
more under the covers then what I would normally think that is does (Just
save the steps).

I occasionally receive an out of disk space error, as well.

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote in message
news:f79pr3jwFHA.3020 (AT) TK2MSFTNGXA01 (DOT) phx.gbl...
Hi Scott,

How are things going? It seems really strange and I would like to see
whether structured storage file limit the time of saving DTS packages.
I
would appreciate it if you could post here to let me know the status of
the
issue. If you have any questions or concerns, please don't hesitate to
let
me know.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader
so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#5

Yes, just sent a response. Sorry for the delay on my end.

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Quote:

Hi Scott,

Thanks fo much for the update.

Have you tried MVP Allan's suggestion storing it as Structured Storage
File?

To find out the root cause of this issue we may need to analyze what was
done internal by SQL Server Enterprise Manager, this work has to be done
by
contacting Microsoft Customer Service and Support. Therefore, we probably
will not be able to resolve the issue through the newsgroups. If the issue
is urgent, I recommend that you open a Support incident with Microsoft
Customer Service and Support so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.

For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Thank you for your patience and cooperation.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS Takes forever to save

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS Takes forever to save - 09-16-2005 , 04:02 PM

Re: DTS Takes forever to save - 09-17-2005 , 02:34 AM

Re: DTS Takes forever to save - 09-18-2005 , 08:54 PM

Re: DTS Takes forever to save - 10-02-2005 , 02:33 PM

Re: DTS Takes forever to save - 10-02-2005 , 02:33 PM