I have a scenario where inspector's synchronize their PDA's at the end of
the day (we use SQL Server Merge Replication). When an inspector is done,
I'd like to kick off a cleanup process (that code would be in a dts
package).

The code on the PDA, if the merge replicaton was successful, would send an
http request to the same IIS that is used during Merge Replication.

*The http call would have to be non-blocking (doesn't matter to the
inspector if cleanup was successful, the end of their "sync" should just
start the process not wait for it to finish).
*For example, if 3 inspector's were sync'ing at approximately the same time,
the dts package should execute it's clean-up steps one request at a time.
In other words, if the dts package is executing, the other two requests
should wait until the 1st one is done. Then the 3rd, should wait for the
2nd to be done. etc.

Is this possible? Ideas?

Thanks!

You should have their synch-ups make an entry into a table. Have a job that
runs every so ofter (1 minute, 5 minutes, whatever) that checks the table to
see if it needs to do any cleanup. If it does, then make the top task as in
process, when it's finished, mark the in-process task as complete and/or
delete it. If a a task is in-process, the job would do nothing else.

You need to consider what to do if a job fails and a process gets marked as
in-process and stays that way. Perhaps some sort of time limit where it gets
changed back to unprocessed if it's still marked in-process.

Robert.


"William Campbell" wrote:

Thought I'd post under my Managed Newsgroup ID, to see if I could garner
more responses.

I had thought of something similar, at first. I was thinking that the HH
could send an asp request to write an entry into a database table - this
would be quick and would free the HH so they could move on without having to
wait. And I was even thinking that this table could then have a trigger
that would then launch the more time-consuming part of the "cleanup"
process. But, I'd really like to easily only work on ONE cleanup at a
time - and I'd like to avoid polling as you suggested. I hate polling!
Especially when 22 of the 24 hours of the day it would be polling for no
reason - but limiting to a set time every day would be silly, just because
of the certain occassions where the sync happens outside the normal 'syncing
window'.

Anyhow, I saw that I could skip the whole "trigger" thing by possibly
calling the dts package directly. If I could get the HTTP to ASP to call
DTS package request to return immediately (and to only run that one dts
process at a time). Looks like I could still get it to return quickly by
writing to the table and launching the trigger to call the dts package ...
but I want to ensure that the cleanup process only runs once at a time. So,
if there are 5 requests concurrently...request 1 runs, finishes. request 2
runs, finishes. etc.

What about if the dts package contains one step in the process - my custom
task (some dll I create). And I set the Package properties to: "Limit the
maximum number of tasks executed in parallel to 1"? Would that work? Would
the requests then run asynchronously? I mean, if the trigger sends 5 quick
requests...but the dts package only runs 1 task in parallel...the other 4
requests, I would think, would have to wait until the custom task inside
this dts package finishes. No?

William Campbell

"Robert Davis" <RobertDavis (AT) discussions (DOT) microsoft.com> wrote

William:

DTS Packages CAN be run from a trigger, but most people don't recommend
doing it. After all DTS is mainly for batch type of operations. Maybe a
better approach is to widen your thinking and run the cleanup once a day.
What is the problem with running the cleanup on everything instead of just
one person's batch? If you insist on doing it that way, read on.

If I were you, I would use a loop structure in your DTS task to accomplish
your "sequential" cleanup operation. Have some way to make a queue of
cleanup requests (either a table in the DB or a file for each cleanup
request). Have the loop open a cleanup request, process it, then go back to
see if another cleanup request exists. If so, do the same operation again,
if not, end. See http://www.sqldts.com/?246 for more information.

Now, you have also talked about single instance execution. You need to have
the DTS Package place a marker that will show it is already executing. If
so, the DTS Package should just end immediately if it finds the marker.
This can be done through either locking in SQL Server or by posting a record
or file somewhere. You need to build in error handling to remove the marker
if the cleanup loop fails and terminates abnormally. This can be come by
building a two package structure where the first package checks for the
marker:

if found--exit
if not--create the marker, execute the loop package, after the loop package
ends (either successfully or by failure) remove the marker

If you are feeling adventuresome, you can also have the first package create
a new cleanup request before checking for the marker. That way your whole
sequence in encased in two DTS packages.

Scott

"MSDN-Managed" <MSDN-Managed (AT) nospam (DOT) nospam> wrote