Sunday, 16 April 2017

Operation messages table in SSISDB

This table stores the log information during the package execution.
SELECT [operation_message_id]
      ,[operation_id]
      ,[message_time]
      ,[message_type]
      ,[message_source_type]
      ,[message]
      ,[extended_info_id]
  FROM [SSISDB].[internal].[operation_messages]
   
Operation Message ID
Auto generated Number.
Operation ID
Operation ID is nothing but the ID of the package (Auto generated ID in Operation table) when the package start executing.
  

Message Type
The type of Message Display. Message type may be
Value
Description
-1
Unknown
120
Error
110
Warning
70
Information
10
Pre-validation
20
Post-validation
30
Pre-execution
40
Post- execution
60
Progress
50
Status Change
100
Query cancel
130
Task failed
90
Diagnostic
200
Custom
400
Non diagnostic
80
Variable Value changed
140
Diagnostic Ex
Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.
The value of the message column for DiagnosticEx is XML text.
Message Source Type
The ID of the type of message source. It may be
Source Type
Description
10
Entry APIs, Such as T-SQL and CLR Stored Procedure
20
External process used to run package (ISServerExec.exc)
30
Package level object
40
Control flow task
50
Control Flow containers
60
Data Flow task

Message
Record the message




Note: If our server is running shortage of space we can truncate this table. It will not affect our operation. Here we are store only package executing details. 

No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts