How can execution of SSIS Package be stopped after a certain task executes?

Question

How can execution of SSIS Package be stopped after a certain task executes?

Victor Proteus

SSC Enthusiast

Points: 151
More actions
January 20, 2019 at 2:42 pm

#370254

QUESTION : HOW CAN I MAKE THE PACKAGE STOP right after sending mail task?
So if mail is sent on the specified condition, (SEE ATTACHED .PDF file with screenshot)
the step ‘Grants on table for TESTING only’ NEVER gets executed, and gets executed only if ‘Check for Existing Interface Table’ task is executed..

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

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

Victor Proteus SSC Enthusiast Points: 151 More actions · Answer 1

i am seeing something like this
https://www.anexinet.com/blog/having-an-ssis-component-wait-until-a-condition-is-met-or-a-time-period-passes/
but it seems so cumbersome/weird/big workaround.

Isn't there just a way to say EXIT? or END ? like it is possible to do in any Prog language and in T-SQL..

TangoVictor SSChampion Points: 10969 More actions · Answer 2

Victor Proteus - Sunday, January 20, 2019 2:42 PM
QUESTION : HOW CAN I MAKE THE PACKAGE STOP right after sending mail task?
So if mail is sent on the specified condition, (SEE ATTACHED .PDF file with screenshot)
the step â€˜Grants on table for TESTING onlyâ€™ NEVER gets executed, and gets executed only if â€˜Check for Existing Interface Tableâ€™ task is executed..

Try adding an expression from the seq container to the Grants on table... task. that is the opposite of the one that allows the email to execute.
So if the email task runs then execution will not continue beyond the seq container.

Note: You may need to change the scope of a variable if that's what you use in those expressions (assuming its scoped to the sequence container or to the ExecSQL task)

If there is no constraint that has a true condition execution STOPS because it has no valid path to take.

TangoVictor SSChampion Points: 10969 More actions · Answer 3

Victor Proteus - Sunday, January 20, 2019 3:41 PM
i am seeing something like this
https://www.anexinet.com/blog/having-an-ssis-component-wait-until-a-condition-is-met-or-a-time-period-passes/
but it seems so cumbersome/weird/big workaround.
Isn't there just a way to say EXIT? or END ? like it is possible to do in any Prog language and in T-SQL..

This is a basic control flow question. Nothing fancy is needed. We do this all the time, check for some condition, if condition is false set variable = FALSE
Then on the precedence constraint you just have an expression that is @Condition == TRUE and then execution only continues if the condition is true.

If false the job ends since there is not path for execution to continue.

edit: sorry didn't realize you posted this, I thought it was a response from someone else... wasn't paying attention.

Victor Proteus SSC Enthusiast Points: 151 More actions · Answer 4

I actually did it via
Dts.TaskResult = (int)ScriptResults.Failure;
in a script task, because we wanted the pkg not just send email and stop execution but report failure as well.

Victor Proteus SSC Enthusiast Points: 151 More actions · Answer 5

unless the design is like the attached, - when tasks are within the container.
The only way in such case is to fail the package right?

TangoVictor SSChampion Points: 10969 More actions · Answer 6

Another way in the send mail task property "force execution result" = Failure.
But what you have will work too.