CODEWITHSUNDEEP
sql-serverssisvisual-studio-2019
Rahul Singh
Rahul Singh

Reputation: 21825

Fail entire SSIS Package in case of Exception

I have a SSIS package with lots of containers and logic. I am running an additional task (which I want to run independently) let's say it acts as an Event Listener. When this separate task is errored, I want to error out the entire package. I thought, it should work by default but to my surprise, it's not:-

enter image description here

I have tried setting both FailPackageOnFailure & FailParentOnFailure properties on both the parent container & the child container but it's not working.

Upvotes: 4

Views: 2327

Answers (3)

Kosta
Kosta

Reputation: 1

This is an old post, but I came up with a similar issue. The container will never fail by itself, it's a dummy object.

The property FailParentOnFailure in the components of the container need to be set to TRUE (or FailPackageOnFailure or both).

In your screenshot, the properties in the Execute SQL component need to be set to TRUE.

Upvotes: 0

SSharma
SSharma

Reputation: 953

I completely agree with Eric's answer. Let me explain to you why raising a flag on error won't work.

I redesign the package so it includes the flag check.
enter image description here
enter image description here
Let's say we have a success flag as user variable which is by default False.

Now we set this variable as True at the end of sequence 2 execution marking the success of all the other tasks in that sequence.
enter image description here


The second part is put into a for loop which runs only once(if at all). It checks if the success variable is true and only then run the inner tasks. It looks like below:

enter image description here The problem is, the success variable check at the start of the for loop will always have the inital value which is false(because it runs in parallel with seq 2 and doesn't wait till seq 2 ends). Hence the second part is never executed. Now change the initial value of success variable to true and run the package again. Play by disabling the error prone tasks and run the package. You will understand how it works.

Upvotes: 3

Eric Brandt
Eric Brandt

Reputation: 8101

I was about to ask exactly the content of your last comment.

Failure of one piece of a package won't make another, unconnected piece stop executing. Once the executing piece is done, the package will fail, but Sequence Container 3 has no way to know what's happening in Sequence Container 2.

Which, honestly, is what we want. If Sequence Container 3 is doing DML, you could leave your data in an unfortunate state if an unrelated failure elsewhere in the package suddenly made everything come to a screeching halt.

If you don't want Sequence Container 3 to run if Sequence Container 2 fails, then just run a precedence constraint from Sequence Container 2 to Sequence Container 3, #3 won't execute until #2 succeeds and the Execute SQL Task succeeds.

Upvotes: 3

Related Questions