SSIS Checking log progress

If your SSIS package logs its progress in the table sysssislog, you can track its progress with the following query. 

Its assumed SSIS Logging is using OnPostExecute and OnPreExecute entries to identity the start and stop of the SSIS task.  

This sql query is used for an SSIS package that pulls data from source to destination table. 

WITH

Post AS (

SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPostExecute' 

),

Pre AS (

SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPreExecute' 

)

SELECT Pre.ID, Pre.source AS [TABLE], Pre.starttime , Post.EndTime ,DATEDIFF(MI,Pre.starttime , Post.EndTime ) as Minutes, Pre.executionid

FROM PRE

LEFT JOIN POST ON Pre.source = Post.source AND Pre.executionid = Post.executionid

order by Pre.starttime DESC