This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

How to monitor 'SQL Integration Services' (all versions) using a single 'Windows Service Monitor'?

Hi all,

I am trying to use 'Windows Service Monitor' to monitor 'SQL Integration Services' (all versions).

The hosts that I have to monitor have different versions of Integration Services running on those.

So the service name could be MsDtsServer100/MsDtsServer110/MsDtsServer120/MsDtsServer130

Is there any way I can monitor this service without first knowing which host is running which version?

Something like what we do in SQL - ServiceName LIKE 'MsDtsServer1%'

I know in the 'Windows Service Monitor' we have to provide exact service name, but is there any way to do this using 'Windows Service Monitor' or any other monitor?

How are you guys monitoring this service?

I am trying to avoid creating 4 different 'Windows Service Monitors' for each version of Integration service as the bigger paint there is to first find out which host is running which version.

  • A few ideas spring to mind.

    First, a script monitor can monitor up to 10 services separately.  It's pretty easy to pattern match with powershell - I do this in a script I run to check newly build SQL cluster instances.

    If you really want to use the service monitoring, then you could do that with several process monitors, then a script monitor to collect the SSIS services, and an alert to monitor the app monitor.

    If that sounds confusing:

    1. script monitor returns list of services - number would be less important if you are only feeding the process monitors - the text field would be a list of service names

    2. custom alert - I would do this in SQL because I would find that easy.  Alert fires when the returned list of services does not match the active process monitors

    3. custom alert action - would enable/disable enough process monitors, and set each active monitor's process name.

    Mathew

  • Thank you for your reply frak.

    I ended up using a SQL Server User Experience monitor using a customized SQL provided by DBA.

  • Great you got a solution together.  I am curious as to what the SQL is though.  Something indirect that tells you the SSIS processes are running?

  • Sorry for delay in response.

    This is the SQL I was using:

    IF OBJECT_ID('tempdb.dbo.hold_sql_services', 'U') IS NOT NULL

      DROP TABLE tempdb.dbo.hold_sql_services;

    IF OBJECT_ID('tempdb.dbo.hold_sql_status', 'U') IS NOT NULL

      DROP TABLE tempdb.dbo.hold_sql_status;

    CREATE TABLE tempdb.dbo.hold_sql_services

       (

         SQL_ServiceName VARCHAR(100)

         )

        

    INSERT INTO tempdb.dbo.hold_sql_services EXEC xp_cmdshell 'sc query type= service state= all |find "MsDtsServer1" |find /V "DISPLAY_NAME"'

    DELETE tempdb.dbo.hold_sql_services WHERE SQL_ServiceName IS NULL

    ALTER TABLE tempdb.dbo.hold_sql_services

        ADD SNID INT IDENTITY(1,1)

    CREATE TABLE tempdb.dbo.hold_sql_status

        (

        SvcStatus VARCHAR(55)

        )

    DECLARE @SvcStatus VARCHAR(100)

    DECLARE @nSvcName VARCHAR(100)

    DECLARE @oSvcName VARCHAR(100)

    DECLARE @gService CURSOR

    DECLARE @cmd VARCHAR(500)

    DECLARE @cSTOP INT

    DECLARE @SvcStatTxt VARCHAR(100)

    SET @gService = CURSOR FOR

    SELECT SQL_ServiceName

    FROM tempdb.dbo.hold_sql_services

    OPEN @gService

    FETCH NEXT

    FROM @gService INTO @oSvcName

    WHILE @@FETCH_STATUS = 0

    BEGIN

       SET @nSvcName=REPLACE(@oSvcName,'SERVICE_NAME: ','')

       --Print @nSvcName

       SET @cmd='sc query ' + @nSvcName + '|find "STATE"'

       --Print @cmd

       INSERT INTO tempdb.dbo.hold_sql_status EXEC xp_cmdshell @cmd

       DELETE tempdb.dbo.hold_sql_status WHERE SvcStatus IS NULL

       FETCH NEXT

       FROM @gService INTO @oSvcName

    END

    CLOSE @gService

    DEALLOCATE @gService

    ALTER TABLE tempdb.dbo.hold_sql_status

        ADD SSID INT IDENTITY(1,1)

    SET NOCOUNT ON

    IF (

    SELECT COUNT(*) FROM tempdb.dbo.hold_sql_services

       ) > 0

    BEGIN

        SELECT

               CASE WHEN CHARINDEX('RUNNING',ss.SvcStatus) > 0 THEN 1

               ELSE 0 END AS ServiceStatus,

       sn.SQL_ServiceName as err_message

          FROM tempdb.dbo.hold_sql_status ss

    INNER JOIN tempdb.dbo.hold_sql_services sn ON sn.SNID=ss.SSID

    END

    ELSE

        SELECT 0 AS ServiceStatus, 'Service name like MsDtsServer1% was not found' as err_message;

    By the way I am trying to create a single powershell script to check status of list of service names and alert if the service names is present but stopped.

    So I won't have to create one monitor for each service to be monitored.

    In message part I am trying to add names of all the services that were present on the server but had state stopped.