June 28, 2011 at 2:46 pm
Hello,
I have 10 different Packages and they are loading data from text file to staging database. I have 1 master package that loads the data from staging to production database.
How can I schedule a package ....like master package knows that all 10 packages executed sucessfully in staging ,,,,and then execute master package.
Means I want to execute master package automatically if all 10 different packages loads successfully....
If one package fails then master package knows that 1 package is failed and do not execute....
Thanks
L
June 28, 2011 at 3:51 pm
here are a couple solutions to that.
First when I need control over package execution what we generally do is call all packages from within another package, you can wrap those 10 packages within a sequence container which will fail the container if any one package fails and set the precedence constraint to only run that last package if the sequence container was successful.
another option is to setup the sql job with a step for each package and have it move sequentially through the packages and quit job on failure or goto a particular step on failure.
My recommendation would be to use precedence constraints within the control flow of a main package to control execution of all other packages. Call this main package from your sql job.
hth
tom
June 28, 2011 at 4:12 pm
You beat me to it Tom...I was just testing the theory of having all 10 in a Sequence Container (concurrent execution) with the 11th package connected to it with a Success constraint and it worked beautifully. I wired one package in the Sequence Container to fail and it stopped short of executing that 11th package.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 4:16 pm
If it is truly a master package, I might follow the advice above. If it's an 11th package that needs execution, you could create a 12th package that manages everything else, that way you aren't messing with the logic or structure of the 11th.
Note that you can also do this in SQL jobs, but it would be a gross "if any package fails, we stop", meaning that you could execute 0-11 packages on any day.
If it that you want to stop all other packages if one fails, or is it that you execute 10 packages, and if any fail you still execute the others?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 28, 2011 at 4:25 pm
OK, here is the 3rd option:
if you are currently running separate jobs for each package add a job step for failure then if the job fails have it go to that step and have that step insert a record in a table to indicate fail, do this for each job.
Then for the master job:
Setup a job that runs the master package.
Setup a second job that runs a T-SQL command that checks the table for a fail value and executes if none of the other jobs failed:
IF NOT EXISTS ( SELECT 1
FROM dbo.JobFail )
EXECUTE msdb.dbo.sp_start_job @job_name = NULL, -- sysname
@job_id = NULL, -- uniqueidentifier
@error_flag = 0, -- int
@server_name = NULL, -- sysname
@step_name = NULL, -- sysname
@output_flag = 0 -- int
TRUNCATE TABLE dbo.JobFail
sp_start_job info - http://msdn.microsoft.com/en-us/library/ms186757.aspx
We've done similar things when we run jobs the monitor other jobs.
good luck
June 28, 2011 at 5:50 pm
Thanks You so much for replying me 🙂
@tom you were saying that I have to create another package(12th package)....In this package , I have to wrap all 10 packages and 1 master package.......Right....????
12th package like
p1 p2 p3 p4 p5 p6 p7 p8 p9 p10(these packages in sequence container??)
Master package(sequence container output -precedent constraints to input of master package??)
Please let me know,,,I am right or not ?
June 28, 2011 at 5:57 pm
Thanks for your reply....:-)
I want to run 10 packages and they are different and not depend on each other.
So if one package fails in sequence container but i want to load 9 package also. Only i don;t want to execute master package......
Thanks
L
June 28, 2011 at 6:23 pm
patla4u (6/28/2011)
Please let me know,,,I am right or not ?
Correct, that's the simplest way to do it.
June 29, 2011 at 9:21 am
If you need to run 10 no matter what, and then only run the 11th if all succeed, I'd either use the package management strategy with a sequence container, or I would let each job insert a value into a table that you check before (or in) the 11th package.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 29, 2011 at 12:18 pm
Thanks.:-)
I also want to know how to do with sql server agent Job ?
can you provide me complete Details ?
Thanks
L
June 29, 2011 at 1:08 pm
Look back at Tom's solution.
If you don't understand that, and can't figure it out, post a specific question. It's not hard.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply