Query the SSIS database for SQL Server Agent job status

Today there will be a short post of a semi useful query for you who want to monitor your Agent jobs without looking into the standard reports.

The query

-- Parts of this procedure is borrowed from [x] (sadly I don't remember the original author/site)
-- This query is for the SSIS database to get an overview/update on the 
-- jobs that have been running. 
SELECT    
  EventMessageId = event_message_id
  --,MessageTime = message_time
  ,EtlBatchId = operation_id -- SSIS execution Id
  ,StartDateTime = start_time
  ,EndDateTime = end_time
  ,DurationInSeconds = DATEDIFF(ss, start_time, end_time)
  ,[Message] = message
  ,PackageName = package_name
  ,EventName = event_name -- SSIS event
  ,MessageSourceName = message_source_name -- The container responsible for the message
  --,PackagePath = package_path
  --,ExecutionPath = execution_path
  --,MessageType = message_type
  --,MessageSourceType = message_source_type
FROM
(
  SELECT  
    em.*
    ,ex.start_time
    ,ex.end_time
  FROM    
    SSISDB.catalog.event_messages em
  INNER JOIN 
    SSISDB.catalog.executions ex ON (em.operation_id = ex.execution_id)
  WHERE    
    ex.folder_name = 'YOUR SSIS PROJECT/FOLDER'
    AND ex.start_time >= DATEADD(hh, -12, GETDATE()) -- if you, like me, check every morning
) AS eMess
/* Put in whatever WHERE predicates you might like*/
WHERE
  1 = 1
  AND event_name = 'OnError'
  --AND package_name = 'Package.dtsx'
  --AND execution_path LIKE '%<some executable>%'
ORDER BY 
  message_time DESC

Output

Seems like I have misloaded the picture. But I think that you get it.

In the next post we will have a look at the MERGE statement and see one way you could implement MERGE within your data warehouse.

//mattssok

mattssok

Leave a Reply

Your email address will not be published. Required fields are marked *