Monitor progress of an SSIS Master package


Monitoring progress of an SSIS Master package (a package that runs other packages) is a painful process so I decided to take stab at writing a query that could pull it off by using the data found in the dbo.sysssislog tables.

For this script to work all packages must be set up with Log provider for SQL Server and it only works for “old school” package deployment mode. Output looks like this:2017-03-10 09_12_19-mRemoteNG - confCons.xml - 172.30.8.203 - Dev:

use msdb
go

DECLARE @ShowErrorMessages int = 0
DECLARE @MasterPackageName varchar(200) = 'MASTER_Stage'

DECLARE @id_min int = (SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageStart' AND source = @MasterPackageName)
DECLARE @id_max int = ISNULL((SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageEnd' AND source = @MasterPackageName AND id > @id_min), 999999999)

;WITH cte AS (
 SELECT 
 PackageStatus = CASE 
 WHEN event IN ('PackageStart','User:PackageStart') THEN '1 Running'
 WHEN event IN ('OnError','User:OnError') THEN '3 Error'
 ELSE '2 Completed'
 END,
 *
 FROM [dbo].[sysssislog]
 WHERE event in ('PackageStart','PackageEnd','User:PackageStart','User:PackageEnd', 'OnError','User:OnError')
 AND id BETWEEN @id_min AND @id_max
 )
SELECT 
 ID = MIN(ID), 
 source, 
 PackageStatus = MAX(PackageStatus),
 StartTime = MIN(StartTime),
 EndTime = CASE WHEN MAX(PackageStatus) = '1 Running' THEN NULL ELSE MAX(endtime) END,
 Elapsed = FORMAT(DATEADD(second, 
 CASE 
 WHEN MAX(PackageStatus) = '1 Running' THEN DATEDIFF(second, MIN(StartTime), GETDATE())
 ELSE DATEDIFF(second, MIN(StartTime), MAX(endtime))
 END, '1900-01-01'), 'd\d HH:mm:ss')
FROM cte
--WHERE source in (SELECT DISTINCT source FROM [dbo].[sysssislog] WHERE Event = 'PackageStart' and id >= @id)
GROUP BY source
--ORDER BY Elapsed desc
ORDER BY MIN(ID)

IF @ShowErrorMessages = 1
 SELECT DISTINCT
 [event]
 ,
 ,[starttime]
 ,endtime
 ,[message]
 FROM [BOS_NBDW_Config].[dbo].[sysssislog]
 WHERE event like '%error%'
 and id BETWEEN @id_min AND @id_max
 ORDER BY source

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s