Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.

Have a data flow which inserts 1000 rows into a table. One of the rows cannot

go because of a constraint on the column. The following is thrown by SQL /

SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:

0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL

Native Client" Hresult: 0x80040E2F Description: "The

statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F

Description: "The INSERT statement conflicted with the CHECK constraint

"CK_cpxx". The conflict occurred in database "POS_ETL",

table "dbo.cpxx", column 'Col1'.".

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE

DB Destination Input" (32)" failed because error code 0xC020907B

occurred, and the error row disposition on "input "OLE DB Destination

Input" (32)" specifies failure on error. An error occurred on the

specified object of the specified component.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component

"OLE DB Destination" (19) failed with error code 0xC0209029. The

identified component returned an error from the ProcessInput method. The error

is specific to the component, but the error is fatal and will cause the Data

Flow task to stop running.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0"

has exited with error code 0xC0209029.

>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)

This causes the onerror event to fire 4 times.

This type of behavior is useless for any error handling of this type as

anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work

than it's worth.

OnFail constraint is the simplest and totally ignoring the onerror event

handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?