August 31, 2009 at 8:31 pm
I'm currently involved in creating a new implementation of a Data Warehouse for my employer. We currently have 50 + packages that have been set up to execute via SQL Jobs. I was wondering if there was a good tool that would allow me to work in complex logic for the execution of these jobs. For instance, wait until jobs A1, A2, and B1 Complete before kicking off C1. Is SQL Agent Service able to do this type of complex scheduling?
thanks in advance.
August 31, 2009 at 9:02 pm
The only way to make agent do that is to do it with a job with multiple steps. But not anything that says the job A and B have to run before job C. But Job 1 with job steps A, B, C could certainly do that. You could even leave the A, B, and C jobs out there unscheduled for use when you have issues and only schedule the job that has all the steps.
CEWII
September 2, 2009 at 3:36 am
I am fairly new to creating packages and running jobs. 🙂 But if I can make a suggestion it would be to create one package in SSIS with different sequence containers. Each container runs the package needed to run at the right stage of the process (see attached image). Then you can just call the one package with the sequence from a job in the Agent.
September 2, 2009 at 6:31 am
I don't see any reason to use sequence containers, you can just use the EXEC package task and connect them. sequence containers seems like overkill. The package that calls all the rest is considered a "master" package and all the others are "slaves".
CEWII
September 2, 2009 at 9:00 am
Moving to SSIS for package execution was my first thought as well when I realized that we needed to execute in parallel. I also used the execute task to execute the SQL Job. Which works, however, after execution it completes, even though the job is still running. Any nifty trick to get the package to wait until the job is complete to report a complete status?
We started moving to the SSIS approach, but once timelines were depicted, management wanted to use SQL Server Agent scheduler to execute the job fleet. So, I'm just trying to figure out the details and drawbacks of both.
September 2, 2009 at 9:26 am
SQL Sentry has an Event Manager tool that schedules SQL Agent jobs, among other things. You can set up job execution chains that have various dependencies.
http://www.sqlsentry.net/event-manager/sql-server-job-scheduler-enterprise.asp
September 2, 2009 at 11:48 am
Actually, I think Southpaw is on the right track with the sequence containers - particularly if jobs A1, A2 and B1 need to run in parallel and all complete prior to starting C1.
September 2, 2009 at 3:27 pm
Eric Pratley (9/2/2009)
Actually, I think Southpaw is on the right track with the sequence containers - particularly if jobs A1, A2 and B1 need to run in parallel and all complete prior to starting C1.
You don't need a sequence container to do that. See the attached file.
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply