How do I view operators in a Notify Operator task in a Maintenance Plan?

Question

In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently left the company and we removed her email account. We also removed her from the list of operators in SQL Agent. However, we have a number of SQL Server servers and instances installed and it would be tedious to go through each maintenance plan on each of those instances to find if she is on the list manually. Is there a way to query each maintenance plan from either within SSMS or PowerShell? We're using SQL Server 2008R2 thru 2014.

Answer 1

Two ways come to mind, there may be more. I prefer the second but that's just my opinion

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\ServerNameHere\DEFAULT\jobserver\Jobs |Select Name, OperatorToEmail

The first line below loads the 2012 assembly since that's the version I'm running on my laptop, adjust to suit your needs.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$srvObject = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'ServerNameHere'
$srvObject.JobServer.Jobs| Select Name, OperatorToEmail

Answer 2

I was able to find pages explaining how Maintenance Plans write and how to query them. Between that and a discussion on how to use PowerShell to query XML, I think I have what I need to pull the maintenance plans in and query them. Since the SSIS package is essentially an XML document, I'm going to need to dust off (and tune up) my XQuery but here's what I have so far:

# TODO: Add a foreach statement, go through $ServerList, 
ouput to DBA database with date, server/instance info with $xmlDocs, 
turn below into a proper generic function

$MyQuery = "SELECT name
      ,CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource
      ,packagetype
       ,id
      ,description
      ,createdate
  FROM msdb.dbo.sysssispackages WHERE [sysssispackages].[packagetype] = 6"
$server = '.'
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=$server; Database=msdb;Integrated Security=true"
$con.open()

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $MyQuery
$cmd.Connection = $con

$as = New-Object System.Data.SqlClient.SqlDataAdapter
$ds = New-Object System.Data.DataSet
$as.SelectCommand = $cmd
$as.Fill($ds);

$xmlDocs = $ds.Tables[0] | %{ $_.PackageSource }