SSIS Job Monitoring system

  • Hi, Im trying to create an web applicaiton that monitors the runtime SSIS jobs. The little application should be able to list down the current status of all the jobs that are deployed in the sql server.

    is there any machanisum throught which we can figure out the runtime jobs in the sql agent.

    Thanks in advance

    gauta

  • Take a look in the MSDB database. In the system tables, there are a few tables that contain job information. Look for tables like dbo.sysjob* .

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hey there, I have created a same,

    Do one thing, deploy 2 sql task on your etl which will update the CustomLog table (which you will create) upon success and Failure.

    Use System variable to get all the details.

    And then fetch the data from that CustmLog table to your web application.

    This will make your task very easy and less error prone.

    Below is the schema of the CusotmLog table whihc you can add to your package

    SELECT TOP 1000 [Id]

    ,[JobID] -- auto incremental

    ,[PackageName] -- comes from system variable package on

    ,[SourceName] -->> its the name of the Task

    ,[SourceDescription]

    ,[EventName]

    ,[EventDate]

    ,[Message]

    FROM [360CDW].[dbo].[CustomLog]

    Hope it was helpful

  • AdityaSingh (5/14/2014)


    Hey there, I have created a same,

    Do one thing, deploy 2 sql task on your etl which will update the CustomLog table (which you will create) upon success and Failure.

    Use System variable to get all the details.

    And then fetch the data from that CustmLog table to your web application.

    This will make your task very easy and less error prone.

    Below is the schema of the CusotmLog table whihc you can add to your package

    SELECT TOP 1000 [Id]

    ,[JobID] -- auto incremental

    ,[PackageName] -- comes from system variable package on

    ,[SourceName] -->> its the name of the Task

    ,[SourceDescription]

    ,[EventName]

    ,[EventDate]

    ,[Message]

    FROM [360CDW].[dbo].[CustomLog]

    Hope it was helpful

    This is fine for ETL logging, but it has nothing to do with SQL Server Agent.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply