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.
-- 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
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.