dts packages take a long time to run

Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:

>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>