August 30, 2009 at 7:11 pm
Hi,
I'm currently trying to setup a data flow that takes care of taking data from a source and inserting it into a destination table. I wanted to setup an onError event handler on the data flow because it would kick in as soon as something causes the package to fail and I would have access to the System::errordescription variables, etc. In the past, I've had a very simple script task that sets these up and my control flow would insert the error log in a table using a separate transaction when my main container would fail.
What I'm wondering is how I could potentially get the value of a data flow item in that handler. For instance, say row with id 1 fails, in my event handler I would like to be able to log that row with id 1 and the values its columns had were the culprit. I don't just want to description of the error, I want to save the contents of the problematic row to be able to keep it if I discard it from my source system.
I've thought maybe of having, inside my data flow, an OnError arrow after my final Ole DB Command linked to a Script Transform (still in the data flow) to save my value inside a package-wide variable but I don't like to access those inside the data flow (not a very natural thing from what I've read) and that would force me to throw the error again afterwards to still get the package to fail.
Any advice or help in this matter would be greatly appreciated.
Regards,
Greg
August 31, 2009 at 5:55 pm
Are you sure want the package to fail? I generally want to set aside the bad data with its error messages and then deal with the exceptions.
That way I can "bag and tag" all the bad data at once. You could at the end of the data flow or the package throw an exception based on a row count (which you can capture in the data flow).
This is not a solution to your expressed problem, I know. Just my 2 cents' worth.
August 31, 2009 at 7:05 pm
Hi,
Thanks for the answer. Well, that might have been a possibility but not in the situation I am in unfortunately.
What I want is to give a certain number of "chances" for a row import to succeed. If any of the rows imported fail, I want the whole data flow to fail (order and integrity is quite important here) and log the contents of the row that caused this error.
What I've done in the end was to have a script component linked to the error output of my OleDB command. When it kicks in, it uses the VariableDispenser class to set some package-level variables and do a FireError() (the sequence container containing the data flow is one DTC transaction). That way my generic error handler can be used and the control flow can be diverted to an error constraint to not only log the package information but also the particular row that caused the first issue encountered. The key here is the word "first". I know technically you'd want to let the data flow continue and log all the rows that were problematic but in my case it's more of a question of rolling back the whole process as soon as one error is encountered and log information regarding that particular row.
But the way this is done is a bit "messy" because I have a bunch of ole db command based on a conditional split so I have to link each of their error outputs to my script component. Ideally, I was looking for a way to do this in the OnError event handler but this is a control flow-level task so I understand I can't really do everything I need there.
Regards,
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply