We have a client who has a package where the main package that call
sub-packages within sub-packages etc. They've asked if this structure can
create overhead issues due to the number of packages being opened. I haven't
seen this listed as a performance concern but ... does someone know what the
overhead implications are of opening additional packages?

I have never tested what the overhead of opening a package would actually be. Whilst there will be some I cannot believe it will
kill you for performance.

If you do not want to hit the server when opening a package then store as a structured Storage file.

Short of that DTS is a client hitting a data source and all rules apply to it as much as any other tool that does this.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Peter Feakins" <PeterFeakins (AT) discussions (DOT) microsoft.com> wrote

We have three-levels of DTS packages. The first level are called "jobs",
which consist entirely of ExecutePackage tasks linked together. The second
level are "schedules", which also consist of ExecutePackage tasks, sometimes
with parallel "threads" that meet at certain "junction" points. The final
level is the one that contains the actual work-horse" DTS packages that
perform the various DataPump, SQLTask, etc. One of our "schedules" contains
around 50 ExecutePackage tasks We experience zero overhead from this
arrangement.

"Peter Feakins" <PeterFeakins (AT) discussions (DOT) microsoft.com> wrote

"Fred Morrison" <fmorrison (AT) erols (DOT) com> wrote

Might I ask how do you manage return status?

At present I'm using the "fail-on-first-error" flag to be able to easy set
workflow properties in the upward levels (callers).

I'd like to be able to let the called package to complete all tasks and at
the same time to (easy!) return a failure status to the caller.

Can anyone suggest a way to achieve this.

Paolo

The way I achieve this is to have the calling package check the status of
each step in the called package, the child packages are called using ActiveX
script and not the 'Execute Package Task'

Paul
"Paolo Fiore" <postaBELIN (AT) paolofiore (DOT) it> wrote

"Paul Smith" <paul (AT) spamno_sagestore (DOT) com> wrote

could be easier

I'd be happy to be able to set a "package fail status" into the caller
before the end in a easy and quick way

but however thanks