SSIS branch failure and success

Question

SSIS branch failure and success

infodemers

SSCommitted

Points: 1513
More actions
September 14, 2012 at 6:36 am

#267767

Hi everyone,
I have a script task with a validation. On failure, I wish to branch on a Execute SQL Task to send an email and then go to the step after the Scrip Task, and on Success, just go to the next step.
If Not (LowSize) Is Nothing Then
Dts.Variables("User::strLowSize").Value = LowSize
Dts.TaskResult = ScriptResults.Failure'Go to step "Send Email SQL Task" then on Success 'Go to step "BulkInsert SQL Task"
Else
Dts.TaskResult = ScriptResults.Success 'Go to step "BulkInsert SQL Task"
End If
Because it is successfull it does not go to next step. Is it because I cannot have a Failure branch and a success branch?

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

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

infodemers (9/14/2012)
Hi everyone,
I have a script task with a validation. On fail, I wish to branch on a Execute SQL Task to send an email and then go to the step after th
___________ ______________
| Script | Failure | Execute |
| Task |--------->| SQL |
|__________| | Task |
|S |_____________|
|u
|c
|c
|e
|s
_____ V
|__________ |

OK. And your question?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

infodemers SSCommitted Points: 1513 More actions · Answer 2

Good point, I accidentaly submited my post before i was finished. now It is ok ! 🙂

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Cool. I'm a bit short of time so can't go into detail.

But what you need to use is a package variable. Set its value in the script depending on the path to be followed (Yes/No, path1 path2, whatever).

And then use precedence constraints after the script task to direct the program flow, depending on the value in the variable.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

infodemers SSCommitted Points: 1513 More actions · Answer 4

I did as on the image, but when Script task complete, it stops, it does not go to execute sp_BulkInsertImportedData Task.

Any reason?

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

I'm sorry, that link does not work for me ("Sorry, that page was not found." is the message I receive)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

infodemers SSCommitted Points: 1513 More actions · Answer 6

How about this link ?

https://picasaweb.google.com/lh/photo/NVtWwU5TG8grxcUtw1YSgNMTjNZETYmyPJy0liipFm0?feat=directlink

Phil Parkin SSC Guru Points: 246959 More actions · Answer 7

OK, I see that one.

I think your problem may be that your flow can go down either one path or the other, but not both.

The Execute ... task is waiting for both inputs to complete before proceeding.

You need to change them to be 'logical or' conditions - so that processing continues when either one completes.

See here for more info.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

infodemers SSCommitted Points: 1513 More actions · Answer 8

Hi again,

I got it working...The link in your last reply helped me a lot!

Thanks a lot again!

😉

Phil Parkin SSC Guru Points: 246959 More actions · Answer 9

Excellent - no problem.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin