March 14, 2012 at 10:12 am
I have an question to the old SSIS Logging Theme.
We want to log (for example):
- ErrorMessage
- ErrorCode
- RowsOK
- RowsFailure
- ...
We have an SSIS Package with 3 Event Handler (PreExecute,PostExecute,OnError) on Package Scope.
We log the Error Information to a Table.
We stand at the following point:
(I have created a test with importing a <NULL> row to a NOT NULL Column.)
If the dataflow comes to the Destination and there is no "error output" for the Destination component , then i can write the whole error message from variable System::ErrorDescription to my logging table (over the onError EventHandler). But i get no information about how many rows fail.
If the dataflow fail, and there is an "error output" on this component, then the component dont fail.
So the System::ErrorDescription is empty but the User::RowsFailure (filled with rowCount component) gives me the row count.
But i want both. I want the detail error message and the failed row count.
What is a solution for these?
Enhancing an Error Output with the Script Component (http://msdn.microsoft.com/en-us/library/ms345163.aspx)
gives only the short error message. And yes, i don't get the Script task to work.
the solution we build is based on:
Instead we use Event Handlers on Package Scope because this needs less implementation work.
greetings
Henrik
March 15, 2012 at 2:12 am
If you implement error handling in the dataflow, then there is no error, it is as simple as that.
If there is no error, there is no description, so you'll have to provide it yourself.
Also, be cautious with using PreExecute and PostExecute on the package level, as these events can be called multiple times during package execution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 15, 2012 at 4:43 am
Koen Verbeeck (3/15/2012)
If you implement error handling in the dataflow, then there is no error, it is as simple as that.If there is no error, there is no description, so you'll have to provide it yourself.
Also, be cautious with using PreExecute and PostExecute on the package level, as these events can be called multiple times during package execution.
Thanx for your info.
I really love the multiple logging 🙂 So i get with only 3 handlers:
PreExeute - Package Begin
PreExexute - Step 1 Begin
PostExecute - Step 1 End
PreExecute - Step 2 Begin
PostExecute - Step 2 End
PostExecute - Package End
The trick is to write System::SourceName to the log.
March 15, 2012 at 4:53 am
Yeah well, I once tried to log the beginning and the end of the package using Pre- and PostExecute and I was a bit surprised my package had started and stopped 6 times 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply