Simple SQL Agent Data Mart ETL

SSIS package to gather data on running jobs

STARTUP sequence container has 2 tasks:

GET SQL Agent Session ID

====================

DECLARE @SESSION_ID INT;

SELECT @SESSION_ID = MAX(session_id)

FROM dbo.syssessions;

SET ? = @SESSION_ID;

GET Refresh Key

=============

DECLARE 

@REFRESH_KEY INT;

SELECT

@REFRESH_KEY = NEXT VALUE FOR [sqlagent].[RefreshKey];

SET ? = @REFRESH_KEY;

TRUNCATE TABLE [sqlagent].[STAGE_JobsRunning];

DATA FLOW: STAGE SQL AGENT Jobs Running

OLE DB SOURCE: GET SQL AGENT Jobs Running

Insert query to get jobs running and associated measures

OLE DB DESTINATION: STAGE_JobsRunning

EXECUTE SQL: Insert row in Refresh table with RefreshKey and SMALLDATETIME