locked
Reg: [SSISDB].[catalog].[operations] RRS feed

  • Question

  • Hi All,

    when i run the query

    select * from [SSISDB].[catalog].[operations]

    in SSISDB in sql server 2012 ,for some operation_ids showing different behaviour as shown in screenshot.

    please tell me the root cause and soiution.


    Friday, March 3, 2017 10:43 AM

All replies

  • Hi 

    This is a bit of a guess. Status code 4 means that the operation failed (See here).

    When SSIS runs a package it begins by first validating the package if the package passes validation it is then executed.

    I think what is happening in your case is that the package is failing validation, and because it failed validation it did not start executing, hence no time in the [Start_time] field. However, after logging the failed validation the operation then completed so it then updated the [end_time]. It's a bit confusing and may have been a bug. 


    Friday, March 3, 2017 12:00 PM
  • Hi Ramesh Reddy Mallidi,

    You could have a try to run following code to see whether there’re some error messages corresponding to these operations. Click here to learn more about catalog.operation_messages.

    select *
    
    FROM [SSISDB].[internal].[operations] AS O 
    INNER JOIN [SSISDB].[internal].[event_messages] AS EM 
    ON EM.operation_id = O.operation_id 
    
    INNER JOIN [SSISDB].[internal].[operation_messages] AS OM 
    ON EM.operation_id = OM.operation_id 
    
    INNER JOIN [SSISDB].[internal].[executions] AS E 
    ON OM.Operation_id = E.EXECUTION_ID 
    
    where OM.message_type = 120 and O.operation_id in (241531,241532)

    Best Regards,

    Albert Zhang


    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, March 6, 2017 3:08 AM
  •  Hi Albert_ Zhang,

    Thanq for responding. i ran your  Query but no data  was comming .


    Monday, March 6, 2017 5:45 AM
  • Hi Ramesh Reddy Mallidi,

    How about just query “operations” and “operation_messages” as following code shows?

    select *
    
    FROM [SSISDB].[internal].[operations] AS O 
    INNER JOIN [SSISDB].[internal].[operation_messages] AS OM 
    ON o.operation_id = OM.operation_id 
    
    where OM.message_type = 120 and O.operation_id in (241531,241532)
    

    Best Regards,

    Albert Zhang


    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, March 7, 2017 11:09 AM