Running one job after the other finishes
-
I have 4 jobs for four different locations ,i want to execute other job only after the previous finishes .
how can i do that in sql server 2005.
or
how can i run a project only after the previous project finishes in ssis.
-
you can schedule them as separate steps in a job using sql server agent, or you could have a global executor parent package that would run each package using the execute package task, and link them with the necessary constraints.
Tom
Life: it twists and turns like a twisty turny thing
-
cant i execute the jobs one after the other?
-
why would you want them in separate jobs and not in one job as separate steps?
Life: it twists and turns like a twisty turny thing
-
if i include the steps of other locations in that job only,than what will happen when i want to run job of only one location.
and afterwards many locations will be added,so that start job at step otion will run all the other location succeding that location.
-
You can start a job using msdb.sp_start_job
-
it seems that perhaps a global executor might be the way to go in this instance. you could group location specific packages under their own containers and use conditions to decide when to run them.
you could control which locations to run using expressions on the constraints, or run all locations at the same time.
Life: it twists and turns like a twisty turny thing
-
can u plz elaborate on this global executor , expressions and constraintsthings more coz its seems to be the solution to my problem.
-
a global executor is simply another package that you use to control the execution of your other packages.
It is just a package executing blocks of code, except these blocks are packages that you have already built.
you can use constraints to control the flow as with a normal package, so you can control when one child package will run based on the results of another child package.
you can group up packages into logical groups using sequence containers, so you could have a container for one location and another container for another. you can then run each container in parallel but ensure that the packages in each container follow on from each other by using success constraints.
Start with a single dummy task and have expression constraints running to each container. You can then specify which container(s) to run based on a variable value.
You need to take care with variables that have the same name and namespace in both the child and parent packages. If you have this, then if you change the variable in the child package the same variable in the parent package will also be updated.
To update a child's variable with a parent variable you need to use parent package configurations in the child package.
hopefully that should be enough to get you going.
tom
Life: it twists and turns like a twisty turny thing
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply