locked
Error Handler In Package RRS feed

  • Question

  • Hello,

    I need your help, i have a package that load data from different tables with specific criteria and need to configure error handler which log any error into database table .

    how can i do that, please if possible, provide clear steps.

    i have tried the following URL with no luck

    https://www.sqlservercentral.com/articles/on-error-logging-by-using-event-handlers

    when i press evaluate for the expression it shows me the below error:

    TITLE: Expression Builder
    ------------------------------

    Expression cannot be evaluated.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE%202015&ProdVer=14.0.23107.0&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    The variable "User::PackageLogID" was not found in the Variables collection. The variable might not exist in the correct scope.

    Attempt to parse the expression ""INSERT INTO [TesTDB].[dbo].[ErrorLog]
    ([PackageName]
    ,[PackageLogID]
    ,[TaskName]
    ,[ErrorCode]
    ,[ErrorMsg]
    ,[PackageDuration]
    ,[ContainerDuration]
    ,[ErrorDate])
    VALUES
    (
    '" + @[System::PackageName] + "'
    ," + (DT_STR, 15, 1252) @[User::PackageLogID] + "
    ,'" + @[System::SourceName] + "'
    ," + (DT_STR, 15, 1252) @[System::ErrorCode] + "
    ,'" + @[System::ErrorDescription] + "'
    ," + (DT_STR,6, 1252) DATEDIFF("ss", @[System::StartTime] ,GETDATE()) + "
    ," + (DT_STR,6, 1252) DATEDIFF("ss", @[System::ContainerStartTime] ,GETDATE()) + "
    , GETDATE()
    )"" failed and returned error code 0xC00470A6. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

     (Microsoft.DataTransformationServices.Controls)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    Tuesday, December 3, 2019 8:52 AM

Answers

  • Hi EL-ABD,

    May I know if you create the variable "User::PackageLogID"?

    If so, please share the scope of the variable "User::PackageLogID".

    Expand the variables in expression builder to check if the variable exists or is in the same scope

    with other variables in the expression.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 4, 2019 6:04 AM

All replies

  • Ensure that you have variable created like below and scope is at Package level. I am not sure what value the author is passing in that blog. The details are not given for this variable. 


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Tuesday, December 3, 2019 9:05 AM
  • Hi EL-ABD,

    May I know if you create the variable "User::PackageLogID"?

    If so, please share the scope of the variable "User::PackageLogID".

    Expand the variables in expression builder to check if the variable exists or is in the same scope

    with other variables in the expression.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 4, 2019 6:04 AM
  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 8:12 AM