none
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

    Tuesday, March 13, 2012 6:02 PM

Answers

  • 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

    • Proposed as answer by Eileen Zhao Thursday, March 15, 2012 7:53 AM
    • Marked as answer by Eileen Zhao Tuesday, March 20, 2012 5:40 AM
    Tuesday, March 13, 2012 6:32 PM

All replies

  • [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

    Tuesday, March 13, 2012 6:09 PM
  • 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] + "'
     )"

    Tuesday, March 13, 2012 6:24 PM
  • 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

    • Proposed as answer by Eileen Zhao Thursday, March 15, 2012 7:53 AM
    • Marked as answer by Eileen Zhao Tuesday, March 20, 2012 5:40 AM
    Tuesday, March 13, 2012 6:32 PM