none
Event Handler returning unexpected Error Code

    Question

  • Hi,

    I am trying to set up an Event Handler to log errors to a table.

    So in my Control Flow pane I set an Execute SQL Task with a deliberate error as follows; -

    Name; - Execute SQL Task with Deliberate Error

    SQLStatement; - SELECT * FROM InvalidTable

     

    Within the Event Handler tab I created a further Execute SQL Statement based on the Event handler OnError

    Name : Log


    Parameter Mapping
    Variable Name System:ErrorCode
    Direction Input
    Data Type LARGE_INTEGER
    Parameter Name 0
    Parameter Size 100

    SQLStatement INSERT INTO dbo.CustomSSISLog  (ErrorCode) VALUES (?)

    Error Output; -

    SSIS package "ErrorLogTemplate.dtsx" starting.

    Error: 0xC002F210 at Execute SQL Task with Deliberate Error, Execute SQL Task: Executing the query "SELECT * FROM InvalidTable" failed with the following error: "Invalid object name 'InvalidTable'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Task failed: Execute SQL Task with Deliberate Error

    Warning: 0x80019002 at ErrorLogTemplate: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "ErrorLogTemplate.dtsx" finished: Failure.

    The program '[2664] ErrorLogTemplate.dtsx: DTS' has exited with code 0 (0x0).

    Within the table CustomSSISLog  a record appeared with the ErrorCode value of -1073548784.
    I expected the value of 0xC002F210 or 3221418512 (dec).

    Any ideas on how to fix this?

    Thanks in advance,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Tuesday, September 21, 2010 3:09 PM

Answers

  • Hello Kieran,

    What I observed, The messages shows in output window in BIDS are "Hexadecimal code" , but values for System::ErrorCode is "Decimal Code".

    If you enable package logging and select Details "OnError" , you will get "Decimal Code".

     

    You might not need to get Hexadecimal Code as you will be executing your package by SQL job or batch and You will see same code(Decimal Code) in pacakge log and your customize table logging.

    Thanks

     

    Tuesday, September 21, 2010 7:12 PM

All replies

  • Custom Logging Task might be the answer (I personally have not tried exactly what you need) - http://www.thejoyofcode.com/SSIS_Custom_Logging_Task_for_Event_Handlers.aspx
    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, September 21, 2010 3:29 PM
    Moderator
  • Thanks Arthur,

    I find the link you provided me helpful. However at first glance the solution in the link seems to be using VB script. I'm quite keen on the old programmer's saying "don't write code unless you have to". So if at all possible I am looking for a solution which doesn't involve .Net code


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Tuesday, September 21, 2010 6:38 PM
  • Hello Kieran,

    What I observed, The messages shows in output window in BIDS are "Hexadecimal code" , but values for System::ErrorCode is "Decimal Code".

    If you enable package logging and select Details "OnError" , you will get "Decimal Code".

     

    You might not need to get Hexadecimal Code as you will be executing your package by SQL job or batch and You will see same code(Decimal Code) in pacakge log and your customize table logging.

    Thanks

     

    Tuesday, September 21, 2010 7:12 PM
  • Hi Aamir,

    I get the same issue when enabling logging; -

    The value -1073548784 is wrote to the column "datacode", this converts to Hex FFFFFFFFC002F210 using the Windows 7 calculator.

    So I was expecting the value  0xC002F210 (hex) or 3221418512 (dec) to appear which is consistent with the output window.

    However when I right click my mouse on the System::ErrorCode and select Hexadecical within the Local variables windows within the Event Handlers scope the result is; - System::ErrorCode {0xc002f210} Int32

    Where ErrorCode {0xc002f210} is consistent with the output display.

    Sorry it's a long time since I manually did Hexadecimal calculations.

    Basically if you click on the DWord radio button within the Windows 7 calculator you get the same result when converting -1073548784 (dec) to hex which is {0xc002f210} which is a valid error code.

    Kind Regards,

    Kieran.

     


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Tuesday, September 21, 2010 7:37 PM