calling SSIS package

Question

calling SSIS package

patla4u

SSCrazy

Points: 2103
More actions
June 28, 2011 at 2:46 pm

#252739

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

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?

TangoVictor SSChampion Points: 10975 More actions · Answer 4

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

patla4u SSCrazy Points: 2103 More actions · Answer 5

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 ?

patla4u SSCrazy Points: 2103 More actions · Answer 6

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

sam.dahl Hall of Fame Points: 3112 More actions · Answer 7

patla4u (6/28/2011)
Please let me know,,,I am right or not ?

Correct, that's the simplest way to do it.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

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.

patla4u SSCrazy Points: 2103 More actions · Answer 9

Thanks.:-)

I also want to know how to do with sql server agent Job ?

can you provide me complete Details ?

Thanks

L

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

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.