February 6, 2008 at 3:16 am
Hello all,
I have a parent package with some children packages.
I need several things to do if the child package was failed.
1. I want to redirect the error row information to a Raw file, so I will know which row failed.
2. despite the "redirect row" that I do in order to redirect the error rows to my error raw file, I want the child package to fail and write to the log the full error description.
3. I want that the parent package won't fail and continue with the other children packages.
The problem is that if I redirect the error rows . the OnError event doesnot execute so the full error description is not written to my log and I dont know what the reason of the failing.
(if I use ComponentMetaData.GetErrorDescription(Row.ErrorCode) I dont get the full error description , I get too general description.
Thanks in advance, 🙂
February 6, 2008 at 6:53 am
The onError event that you have created on the dataflow task will have a system variable called "propagate". You can either set this manually or dynamically using an expression.
This variable controls whether the event is passed up the package hierarchy or not. If propagate is set to FALSE, you will be able to perform your own custom error handling and logging within the event handler, and because the parent containers will be "unaware" of the failure, you can continue with the control flow.
I hope this helps.
PS: Just re read your post - if you redirect your rows you could pass them through a rowcount component. In your control flow add a script component straight after the dataflow task that reads the rowcount variable. If @ErrorRowcount > 0 Dts.TaskResult = Dts.Results.Failure. Place both of these components into a sequence container and create the event handler here instead.
Kindest Regards,
Frank Bazan
February 6, 2008 at 11:15 pm
Thank u for your answer.
My current problem is that when I Redirect the error rows, the error description is not written to the log when I select the "OnError" event (in the log events list that will be logged).
The "OnError" event does not happen. not the event nor the log line.
I solve the parent failling by giving the "FailParentOnFailour" parameter to "false" and in the parent the connection between the children is not "OnSucsses" but "Complition". so now , if the child fail , the parent does not fail.
But now the child is not failling (because I Redirect the rows).
This is my current problem.
Thank u.
(sorry about my Enlish,I hope I was understood)
February 7, 2008 at 4:25 am
Hi ital
My current problem is that when I Redirect the error rows, the error description is not written to the log when I select the "OnError" event (in the log events list that will be logged).
Often the errors descriptions are not particularly "descriptive". Especially for redirected rows. What I was thinking is that, initially you would write the error rows to your raw file together with any error descriptions that are available. At this point no error events would have occurred.
The "OnError" event does not happen. not the event nor the log line.
By adding a script component after the dataflow, you can force an error event to happen. Then you create an event handler on the sequence container and in this you create your own custom logging.
Remember that you can create your own error messages if you want to. In other words you know there were redirected rows because @errorrowcount > 0 and therefore you could add your own message to the effect of "Package failed due to transform error. Problem rows in raw file blah blah"
I solve the parent failling by giving the "FailParentOnFailour" parameter to "false" and in the parent the connection between the children is not "OnSucsses" but "Complition". so now , if the child fail , the parent does not fail.
The only issue you may have with this is that this behaviour will occur in all circumstances. If say some errors are more serious than others, then you might want to control which failures cause the parent to fail. The set up you describe above sounds like no matter what happens in the child package, the parent will keep on running. If however you suppress error messages in the tasks that don't matter, then the ones that do will still cause the parent to fail. I guess it depends on the logic you want to enforce.
Cheers
Frank
PS: See this link from the msdn forum.
Kindest Regards,
Frank Bazan
August 18, 2008 at 9:12 am
My gut feeling is that your package doesn't even fire OnError because it failed at the validation level. You can put a debug message in OnPreValidate event to see what happens.
May 12, 2009 at 8:36 am
Hi Frank,
I tried your suggestion of adding a script component after the data flow task and forcing it to fail. However, I am still missing the original error message that had occured in the data flow. I am trying to insert rows in the data flow task and few of them get redirected due to a pk/fk issue. The event handler tells me that the Script task has failed, as the @System::ErrorDescription no longer contains the Foreign key error that occured due to the insert!!
This is the content of the automated email sent from the OnError event -"The Script returned a failure result."
Is there any way to retain the error in data flow?
Any suggestions will really be helpful.
Many thanks in advance.
May 12, 2009 at 10:20 am
Yes,
You can write a transformation script in your dataflow to capture the error description on your failing rows.
I can't remember off the top of my head what the syntax is for it, however there is an article on how to do it on SSIS Junkie blog.
If I have time later on, I'll come back to this post and add a proper link.
A brief summary then of what the package would look like.
Dataflow
--------
Datasource > Destination > Redirected rows > Rowcount > Script Component (Get Error description) > Error Destination
Controlflow
----------
[Dataflow] > PrecedenceConstraint (if @@ErrorRows > 0) > Script Component (Force Error)
I can probably come up with an example for you if you're still stuck.
Let me know how it goes
HTH
Kindest Regards,
Frank Bazan
May 12, 2009 at 10:24 am
Found the link
Good luck
Kindest Regards,
Frank Bazan
May 12, 2009 at 1:56 pm
Hi Frank,
Yes I have already used this error Description inside the data flow, but it is a generic one eg: It will say "data integrity violation'; but the @System::ErrorDescription in the event handler gives the actual error thrown by SQL server with the Foreign key constraint name that failed. It gives a more specific and detailed error, as if you were running the insert on sql command line. This helps a lot when there are multiple FK's in the table and a lot of rows are rejected while inserting.
I tried using System::errorDescription directly in the script of data flow task, but unfortunately it says variable not found as this one is only available to the error handler 🙁
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply