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
