none
Find out if SQL Server Services are running through SSIS 2008

    Question

  • SSIS 2008

    I'm trying to implement a package that will run every 5 mins or 10 mins and checks all the SQL Server services to see if it's
    running. The following are the services I'm
    interested
    MSSQLSERVER
    SQLSERVERAGENT

    I have default and named instances on multiple servers as usual.

    Here's what I have and figured I'll need:
    View - I get a list of instance name and service name from
    a view. Why view? The view essentially splits and decodes named instance. The
    view also looks at a table (that I'll populate with new server and a Boolean
    field that turns on and off monitoring). Anyway, this is an example of what I
    get on that view.
    ServerName | ServiceName
    serverA | MSSQLServer
    serverA | SQLSERVERAGENT
    serverA | MSSQLServer$namedinstance
    serverA | SQLSERVERAGENT
    serverA | MsDtsServer

    On the package, I have:
    1) Execute SQL Task -
    select * from the view.
    And I'm passing the resultset out (I think). This task executes without any issues.

    2) Foreach Loop Container
    connected to Execute Sql Task. And on the Foreach Loop Editor, I have changed it to Foreach ADO Enumerator and ADO Object source variable to User::ConnVariable (from Execute Sql Task)

    I figured I'll need
    1) WMI Event Watcher inside the Foreach Loop Container. OR possibly a Execute SQL Task to query the status

    2) And Data Flow Task to store the status of each services

    3) Outside of the Foreach Loop Container, I figured I'll need Send Mail Task to email if there's a service down.

    Am I somewhat on the right track? Any help / guidance / sample will be greatly appreciated.

    Thanks.

    Friday, November 08, 2013 10:11 PM

All replies