How do i load Error Log table using system variables when error occurs?

Question

Hi, I am using this link http://www.sqlservercentral.com/articles/SQL+Server/66387/  to create OnError Logging on my SSIS package. but the columns that i need to populate in Error Log table are :  

[ErrorNumber]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]
,[ErrorLine]
,[ErrorMessage]  

Which system variables i should use to populate these fields? i see only @[System::ErrorDescription] for [ErrorMessage].

Or is there any other way i can populate these fields when an error occurs in SSIS?

Thanks

Answer 1

[ErrorLine] - there is no lines unless you are in a Script task/component, if this is what you need then you need some special code (I have not experimented with) that catches in the Try-Catch the line number, based on what I know VSTA (script engine) wouldn't simply catch it.

[ErrorNumber] = ErrorCode - a HEX code, do not see a problem, do you?

[ErrorSeverity] - you must define perhaps your own enum for this othewise all errors in SSIS are fatal;

[ErrorState] - I have no clue what this can be;

[ErrorProcedure] must = TaskName in the example, I 'd say it is the container;

[ErrorMessage] = ErrorMsg from the example, what is the issue?

---

Arthur My Blog

Answer 2

Thanks for your reply Arthur,

I got ErrorNumber, ErrorProcedure and Error Message from your answer. but i want to load rest of the 3 fields.

I am using the following script in ExecuteSQLtask expression and it works for ErrorNumber, ErrorProcedure and Error Message.

"INSERT INTO [PWO_CONTROL_PANEL].[dbo].[tblErrorLog]
 ([ErrorNumber]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]
,[ErrorLine]
,[ErrorMessage])
Values (
'" + (DT_STR, 15, 1252) @[System::ErrorCode] + "'
, ''
,''
,'" + @[System::SourceName] + "'
,''
,'" + @[System::ErrorDescription] + "'
 )"

Answer 3

Unfortunately, I cannot get you the error_state and severity, these are imaginary values in your design,

regarding the error line number it is problematic as far as I remember, but I am willing to experiment here with you.

Can you add the code below to your Script Task? And in some cases since like I said, there are lines of code involved the value will be not assigned or set to default.

try
{
    throw new Exception();
}
catch (Exception ex)
{
    // Get stack trace for the exception with source file information
    var st = new StackTrace(ex, true);
    // Get the top stack frame
    var frame = st.GetFrame(0);
    // Get the line number from the stack frame
    var line = frame.GetFileLineNumber();
}

---

Arthur My Blog