Package Flow - How To Ensure Step B does notstart until A is Done

Question

Post reply

Package Flow - How To Ensure Step B does notstart until A is Done

YSLGuru

SSC-Insane

Points: 21661
More actions
April 11, 2010 at 2:26 pm

#233669

Please be gentle as I am a SSIS2005 novice so my comments or questions may sound a bit dumbed down.
This is tough to properly describe so bear with me. In order to better explain this I'm going to use a simplified example.
Lets assume I have a package that consist of 3 SQL Job calls.
Step 1 – Calls SQL Job A
Step2 – Calls SQL Job B
Step 3 – Calls SQL Job C
There is a green connector/line between Step 1 &Step 2 and also between Step 2 & Step 3. This way Step 2 executes only if the execution of Step 1 returns SUCCESS. While this part does work, the problem is that the call to Step 2 to launch SQL Job B, is happening before Job A is complete.
#1: Is there a way in SSIS so that the next step in the package flow is not called until the one before it completes? The green connector line does not call the next step until the one before4 returns success but the problem is with when a step in the package flow returns that status if SUCCESS. In this case its returning success after the Job successfully starts and not after the Job completes successfully. The end result is …
Step 1 calls Job A successfully. Job A starts running. Package flow goes to step 2 and calls Job B even though the Job called in the step before is still running. When Job B starts successfully the package flow goes to Step 3 and calls Job C and so we end up with all 3 Jobs running at the same time.
#2: Is there a way in an SSIS package to prevent flow from step to step until the work called in the prior step is complete?
Thanks and please don’t hesitate to ask questions as I realize the above my not be clear. I re-wrote it several times just to get what you see here so I realize its confusing.
Kindest Regards,
Just say No to Facebook!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Please check out Jonathan Kehayias While Loop Solution at the following URL:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/d5cb4004-02fb-4b41-a321-5197f8105941[/URL]

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jasonmorris SSCrazy Points: 2492 More actions · Answer 2

Hi,

The connectors between steps can be 'on success', 'on failure' or 'on completion'.

I'd say it was the last one you require.

Have fun.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

jasonmorris (4/12/2010)
Hi,
The connectors between steps can be 'on success', 'on failure' or 'on completion'.
I'd say it was the last one you require.
Have fun.

No, the precedence constraint 'On Completion' means that the next task will only begin if the previous SSIS task has completed, whether it was succesful or not. It has nothing to do with SQL Jobs.

A plausible solution was given by Welsh Corgi, i.e. use a while loop to chech the status of the job.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Thanks to everyone for taking a minute to help me on this SSIS issue.

I'm trying very hard to like SISS but it feels very muc like SSIS doesn't want to be liked.

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

I just read thru the posts at the Kehayias thread (http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/d5cb4004-02fb-4b41-a321-5197f8105941) on this, (provided thanks to Weksh Corgi.

Luckily for me and my specific scenario I can go with 'ON COMPLETION' and not have to worry about whether the prior step was sucessful or not because the individual steps of the package are independent of each other. I definately don't want multiple steps running at the same time but execution of each step is not dependent on the sucess of the one before it; they just need to wait till the step before completes before the next one begins.

The thread by Kehayias has 2 possible answers to this problem although I'd call them hacks more then solutions or work-a-rounds since there need comes from the fact that SSIS is missing a key ability, to allow package flow based on the combination of ON COMPLETE & ON SUCESS.

Does anyone know if this is corrected in SSIS 2008 or at least by SSIS 2010?

Geesh, I try hard to love SSIS but it trys harder to not be liked.

Kindest Regards,

Just say No to Facebook!