I have a DTS package that downloads a large number of tables from a remote database into SQL Server. Each download has its own source and destination connection and a datapump. I am still experimenting with the number of parallel tasks to use to get the optimal download time. What I need to is to change the order in which the datapumps are processed in a manner that is easier than copying the information from one datapump to another. I have seen this done before but can't remember how to do it. Any help would be appreciated.

Example:

Currently processing datapump1, then 2 then 3 then 4 then 5

want to change it to
Datapump 4, then 3, then 1, then 5 then 2.

You can do this using Workflow Constraints

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

connection and a datapump. I am still experimenting with the number of
parallel tasks to use to get the optimal download time. What I need to is to
change the order in which the datapumps are processed in a manner that is
easier than copying the information from one datapump to another. I have
seen this done before but can't remember how to do it. Any help would be
appreciated.

You could still use Workflow and have multiple ON SUCCESS constraints going
to 1 following DataPump.

You can also Disable/Enable steps using Active X Script

Multiple Paths in Workflow
(http://www.sqldts.com/default.aspx?218)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

parallel tasks running the example would be

Is there no simple way of just resequencing the execution order of the steps without resorting to workflow constraints which could turn out to be quite complex.

I realise that I could potentially dump the package into VB reorder it in VB and then recreate the package from the VB (which version of VB is it? I'm using SQL Server 2000) which seems a little excessive.

"Allan Mitchell" wrote:

From your previous posts I presume you have all these Datapumps in one large
package?

If so, I'd suggest you break them out into individual packages. This will
allow a greater level of flexibility when trying to optimise the execution
order. I've done this on a couple of DTS projects. The largest was over 180
packages in a 4 level hierarchy.

Then create a master package that controls the order in which the packages
are executed. This can be done using ExecutePackage tasks and workflow as
Alan has suggested or via ActiveScript task.

For parallel execution take a look at setting up an SQL Agent job to run the
package and then calling sp_start_job to run it. This way you aren't waiting
for the package to complete before executing another package.

Hope this helps
Phill

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

quite complex.
using SQL Server 2000) which seems a little excessive.