Is there any way, without using SQL Agent, to run dts's asyncronously?

I have 12 dts's - based on regional info, depending on several factors I
need to run any number of them, via a gui front-end, where a user selects
which of the 'regions' to process.

I don't know how to process them one at a time. I am unable to put them into
a Job, as all 12 may not be run at the same time, and there's no particular
defined number that could be run...

Any ideas would be much appreciated?

One way to do it would be to make EACH of them a job, and then use the gui
to start whichever jobs are necessary (depending on what your user selects).

Another way to do it would be to have a "master" DTS job that uses global
variables in an ActiveX script to fire off whichever (of the 12 regional
packages) need to be run. Those global variables could then be set by the
gui as the user selects the desired regions, with a final button to run the
master DTS job.

There are other ways, such as using a stored procedure, or putting all the
DTS packages in a chain and then letting each regional "step" determine
whether it should run via a global variable, but at any rate, best of luck.


"Ben W" <benwatt26 (AT) yahoo (DOT) com.delete> wrote

Correct me of I'm wrong, but making them each a job, and starting each job
on its own (by sp_start_job for eg) would kick all of the jobs off and they
would all be running together. I want each dts to execute, then when it's
completed, the next dts to execute.

I am essentially creating a queue of dts's to run. And whichever of the 12
dts's I have added to the queue, they should execute one at a time, one
after the other.

If I give you a little more of the background, it may help you to help me!
I have 12 text files which I recieve from various coutry's. At month-end I
must process these text files into a SQL Table. Each file loaded into one
table, processed and moved into a final table. Each dts essentially goes:
1. Truncate load table
2. Load file into load table.
3. Do some sql statements/update on the load table.
4. Move data into Final table.

The problem is that come of the text files may/may not exist, and I have a
user interface to tell me which ones exist. I can easily kick the dts of
with dtsrun, but they nede to run one at a time.

Thus each dts must run on its own, as the load table is truncated and
re-populated each time. Running them together would cause a lot of
truncate/load problems.

May thanks for the help!







"J O Holloway" <jholloway (AT) pinncorp (DOT) com> wrote

Thanks very much for those approaches, although in my haste I used yet a
third alternative..

Since our front-end user explicitly chooses which of the 12 files are
available, I am able to dynamically create a whole new scheduled job.

By use of

sp_delete_job (delete the job if it exists)
sp_add_job (create a new instance of the same job)
sp_add_jobstep (create step 1 of n)
sp_add_jobstep (create step .. of n)
sp_add_jobstep (create step n of n)
sp_add_jobserver

I was able to have a whole new scheduled job, where the list of dts's will
be executed in order.

I have also looked into the solutions you have provided and am sure they
will come in handy in the future, for any number of reasons.

Thanks very much for your help.





"J O Holloway" <jholloway (AT) pinncorp (DOT) com> wrote