How to process package even one task failed but allow other tasks to process

Question

How to process package even one task failed but allow other tasks to process

Shaun2012

Hall of Fame

Points: 3214
More actions
October 24, 2013 at 9:25 am

#279071

Hello All,
I have package with 10 data flow taks and for each data flow task one table is truncating and in the data flow task the table is inserted new rows. But i want the package process even one task failed but allow other tasks to process and there is no sequence containter used. Here is the sample
Truncate1 ->Data Flow Task 1
Truncate2 ->Data Flow Task 2
Truncate3->Data Flow Task 3
Truncate4 ->Data Flow Task4

Viewing 15 posts - 1 through 15 (of 16 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

All you need to do is change precedence constraint from Success to Completion.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 2

i also wanted e-mail to send out which one got failed and which success

Shaun

kl25 Hall of Fame Points: 3752 More actions · Answer 3

Shaun2012 (10/24/2013)
i also wanted e-mail to send out which one got failed and which success

There are probably more elegant ways to do this but a method I've used is to:

1. Set up a variable to hold a result value for each task (one per task tracked)

2. Set an 'OnTaskFailed' event handler for each task.

3. Use a SQL Task in the event handler to assign a value to the variable to represent the failure.

4. Use the variable values to report back in an email the success or failure of each task.

Edit: Added a bit more detail about the use of the event handler.

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 4

i am able to do but how to set up.. which task failed , only only send out message with the failed variable only

kl25 Hall of Fame Points: 3752 More actions · Answer 5

Shaun2012 (10/25/2013)
i am able to do but how to set up.. which task failed , only only send out message with the failed variable only

I use an variable expression for the outcome and then place it in the MessageSource for the email. Here's an example using results from one package. Adding tabs and new lines can be helpful in the final message if you have lots of packages to report.

Setup:

@IntResultPackage1 (int32), default of 47

@StrResults (string)

Failure SQL Task populates a value of 9 (or some other generic number) to the result variable so the end value is 9 if there's a failure and 47 (or some other generic number) if there's a success.

Expression for @StrResults:

"Package 1 Outcome: " + ((@[User::IntResultsPackage1]!=47)?("Failure"): ("Success"))

Remove the space after ("Failure"): ... I couldn't figure out how to post without the sad emoticon for :left paren ... 🙁 😀

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 6

Shaun2012

Hall of Fame

Points: 3214

October 25, 2013 at 10:59 am

#1661689

thank you its working now..:-):-)

regards

Shaun

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 7

i am having issues in displaying it today..

Please view the snapshot i had ulploaded

and In the message source:

(( @[User::Variable1] !=47)?("F4"): ( "Success" ))

Can you please help me out..

Shaun.

kl25 Hall of Fame Points: 3752 More actions · Answer 8

kl25

Hall of Fame

Points: 3752

October 28, 2013 at 8:37 am

#1661999

What error are you receiving?

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 9

[Execute SQL Task] Error: Executing the query "f" failed with the following error: "ORA-00900: invalid SQL statement

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

kl25 Hall of Fame Points: 3752 More actions · Answer 10

What's the query? Has it been changed since you got the process to work originally?

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 11

no... i have to taks but one is working fine and this one is not showing the fail message.

I have sequnece container which to flow task are running parrallel and if one anyone fails it will process the otjer task and e-mail send out which task has proecessed success or fail.

kl25 Hall of Fame Points: 3752 More actions · Answer 12

Not sure there's enough information here to help you troubleshoot this latest problem but here's one possibility for you to check. If this is the second package you've added to check for success/failure and you copied the expression from the example earlier in this thread, you may have the space after ("F4"): . The code you posted shows the space. If it's actually in your package, remove the space.

Shaun2012 (10/28/2013)
i am having issues in displaying it today..
...In the message source:
(( @[User::Variable1] !=47)?("F4"): ( "Success" ))

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 13

is there any other way u cann help me .. from the start..as i had removed spaces but same message..Or can u send me one small sample..

shaun

kl25 Hall of Fame Points: 3752 More actions · Answer 14

Ok, just noticed something in your image that I missed the first time. You've got Variable1 for holding a result set. But you've mapped it as a parameter, not as a result set. Delete the Parameter Mapping and go to the Result Set section. Map it there.

Review this Technet article for specifics on how to map a result set to a variable.How to: Map Result Sets to Variables in an Execute SQL Task