none
SSIS Script Task FireInformation Messages Missing After FireError RRS feed

  • Question

  • I am using a SSIS Script Task and FireInformation messages to log progress.  This works fine if the task succeeds but the messages are missing when the task fails and I issue a FireError message.  This package is running via SQL Agent and executing the package out of the SSISDB.
    Monday, October 7, 2019 4:19 PM

All replies

  • Hi DrBobH,

    Please share your SSIS Script Task source code.

    How do you check the logged error messages?

    You can run the following SQL in SSMS:

    use SSISDB;
    go
    
    SELECT      *
    FROM        catalog.operation_messages  AS MSG
    INNER JOIN  catalog.operations          AS OPR
        ON      OPR.operation_id            = MSG.operation_id
    WHERE       MSG.message_type            IN (120, 130)
    ORDER BY   MSG.message_time DESC;

    Or the more polished version of it:

    Select    o.Object_Name                                [Project Name]  
            ,Replace(e.package_name    , '.dtsx', '')        [Package Name]  
            ,o.start_time                                [Start Time]  
            ,o.end_time                                    [End Time]  
            ,e.message_source_name                        [Message Source Name] --[Tool (Step)]  
            ,e.event_name                                [Event Name]                  
            ,e.subcomponent_name                        [SubComponent Name]  
            ,e.message_code                                [Message Code]  
            ,m.message_time                                [Event Time] --[Error Date]  
            ,m.message                                    [Error Message]  
            ,m.message_time                                [Error Date]  
            ,o.caller_name                                [Caller Name]  
            ,o.Stopped_By_Name                            [Stoped By]  
            ,Row_Number() Over(Partition BY m.operation_id Order By m.message_source_type Desc) [Source Type Order]  
    From    SSISDB.internal.operations o  
            Join SSISDB.internal.operation_messages m  
                On    o.operation_id = m.operation_id  
            Join SSISDB.internal.event_messages e  
                On    m.operation_id = e.operation_id  
                And    m.operation_message_id = e.event_message_id  
    Where    m.message_type = 120  
    Order By o.end_time Desc  
            ,o.Object_Name  
            ,[Source Type Order] ASC;


    Monday, October 7, 2019 4:32 PM
  • I have checked the messages in SSISDB.  When I check a good run (status = 7), I get the OnInformation messages.  When I check an error run (status = 4), I get only the OnError messages from the Script Task.

    The script task always outputs FireInformation messages.  It only uses FireError when it detects an error.

    There is another Script Task that runs first and it outputs OnInformation as well, but these do not get lost on an error run.

    DECLARE @folder_name AS nvarchar(128) = N'<folder>';
    DECLARE @project_name AS nvarchar(128) = N'<Project>';
    DECLARE @package_name AS nvarchar(260) = N'<Package>.dtsx';

    SELECT     opmsg.[operation_message_id] as [event_message_id],
               opmsg.[operation_id],
               opmsg.[message_time],
               opmsg.[message_type],
               opmsg.[message_source_type],  
               eventmsg.[event_name],
               eventmsg.[threadID],
               CAST(SUBSTRING(opmsg.[message], 1, 4000) AS nvarchar(4000)) AS [message]
    FROM       [internal].[operation_messages] opmsg
    LEFT JOIN [internal].[event_messages] eventmsg
               ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
    WHERE    opmsg.[operation_id] = (
                    SELECT
                              MAX([execution_id])        AS [execution_id]
                    FROM    [catalog].[executions]
                    WHERE    [status] = 4    -- (4 or 7)
                     AND    [folder_name] = @folder_name
                     AND    [project_name] = @project_name
                     AND    [package_name] = @package_name )
     AND    eventmsg.[execution_path] = '\xxx\yyyy'
     AND    eventmsg.[event_name] IN ( 'OnInformation', 'OnError' )
    ORDER BY  [event_message_id];
    GO

    Monday, October 7, 2019 6:40 PM
  • Hi DrBobH,

    A Script Task is VB or C# code driven where I expected you having the code like

    Dts.Events.FireInformation(0, "Script Task Example", _  
                    "Internet connection detected.", String.Empty, 0, fireAgain)

    This is what we would like us to show.


    Arthur

    MyBlog


    Twitter

    Monday, October 7, 2019 7:26 PM
    Moderator
  • Hi DrBobH,

    Let's clarify few things:

    1. Assumption is that your SSIS Script Task is logging both types of entries: FireInformation and FireError.
      I asked you "...Please share your SSIS Script Task source code...".
    2. There is a big difference between our SQL statements.
      Please try to run my refined version, and if needed just add your additional filters on folder, project, and package.

    If you are using try/catch/finally error handling, the flow will be redirected by the error handling to the catch branch, and all consecutive FireInformation events logging after that error will never happen.



    Monday, October 7, 2019 7:36 PM
  • Hi DrBobH,

    We could also use Integration Services (SSIS) Logging to log the error messages.

    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

    Tuesday, October 8, 2019 7:49 AM
  • Hi Mona, et. al.:

    Thanks for your help.

    It looks like my script may sometimes fail before it gets to the information messages and that led me to think the messages were getting lost.

    I will put in some more try/catch to see if I can isolate the error condition.

    Regards,
    Bob H.

    Tuesday, October 8, 2019 2:55 PM
  • Hi Bob,

    As I mentioned earlier: "...If you are using try/catch/finally error handling, the flow will be redirected by the error handling to the catch branch, and all consecutive FireInformation events logging after that error will never happen...".

    Please share your SSIS Script Task source code...

    Tuesday, October 8, 2019 3:06 PM