Hello,

I'm hoping someone here might be able to offer some advice related to
an SSIS design quandry I'm working on. Pardon the long post, but I want
to make sure I'm communicating clearly.

For lack of a better term, what I'm working on is "high level job
control," or perhaps we could call it "high level workflow."

The system I'm working on has several "jobs" that cooperate around a
central data store. Some jobs import data (different jobs from
different sources). Other jobs download files that will later need to
be imported. Some jobs export data and upload it somewhere. Some jobs
send status emails at a specified time. Some jobs perform system
maintanence.

These jobs form something of a "web," with a SQL Server 2005 instance,
with multiple databases, at the center of the web. As you could
imagine, there are dependencies within the jobs. In analyzing my
requirements, I came up with a couple lists to describe my needs
relative to "job control":

QUALITIES JOBS CAN HAVE:

- Some jobs can be run in parallel, some must run only after
preceding jobs have succeeded
- Jobs run at different freqencies (daily, weekly, multiple times
during the day)
- Some jobs need to run by themselves when no other jobs are
running because they tax resources or place transactional demands on
the database
- Some jobs need to time out (like an FTP upload or download that
has hung)
- Some jobs need to be retried, with an interval between each
retry, and a limit on the number of retries

REASONS A JOB SHOULD *NOT* RUN:

- The preceding job has not completed/succeeded yet
- The preceding job has failed
- We are in a "global" blackout time in which no jobs should run
(for example, during system maintenance)
- We are within a blackout time for this particular job
- Today is a day that this job should not run (for example, certain
jobs should only run Monday through Friday)
- Not yet this job's scheduled time (or the job has already run and
should not run until a specified time in the future)
- Not enough time has passed since the completion of the last run
of this job (for recurring jobs that are not tied to clock time, but
rather certain intervals)

Hopefully this gives you an idea of what I mean by "job control." When
I first started reading about SSIS and heard that it had "workflow"
capabilities, I got excited because that would be plumbing I would not
need to design and build myself. However, I've come to realize that the
kind of centralized job control intelligence capability I'm looking for
is not there.

It seems clear that I *could* use pure SSIS for my "high level job
control," but that would involve scattering the "knowledge" of job
control throughout the system, hidden in a maze of precedent constraint
expressions between high level sequence containers in a giant
monolithic package. I fear that going down that road will lead to a
system that is hard to understand, maintain, and troubleshoot--which
describes exactly the mess of scripts, DTS packages, .NET programs, and
stored procedures that I'm trying to replace.

So I'm thinking that I'm going to have to write a standalone .NET
application that decides which "jobs" to run when and launches and
monitors SSIS packages programatically. Each "job" would be represented
as a package, and packages could remain blissfully ignorant of other
packages, leaving high level control to the central facility. SSIS is
workflow would still take care of parallelization, sequencing, and
looping within a given package.

Does that sound about right? Or am I missing some capabilities of SSIS?

Thanks,
Daniel Read
http://www.developerdotstar.com