Building an SSIS Multi-Server Health Sweep with Automated Email Reports
If you are responsible for multiple SQL Server instances, one of the most useful things you can build is a centralized health sweep package. The idea is straightforward: maintain a list of all your servers in one place, loop through them with SSIS, collect the health data you care about, write it to a central repository, and send email reports automatically. No third-party monitoring tool required — just SSIS, Database Mail, and a couple of staging tables.
This guide walks through building an SSIS package that reports SQL Agent job failures over the last N hours, scans SQL error logs for failed logins and high-severity events, and alerts on databases and files approaching a free space threshold before autogrowth kicks in. Every query, every table, and every SSIS task is covered end to end.
Platform compatibility: SQL Server 2017 and later. The email generation uses STRING_AGG which requires SQL Server 2017+. A FOR XML PATH alternative is provided in Section 4 for SQL Server 2016. The package design works on SQL Server 2016 and SQL Server 2012 with small syntax adjustments. If you are evaluating modernization options, Microsoft Fabric Data Pipelines provides a cloud-native migration path for SSIS workloads — SSIS in Fabric went GA in 2025 — but on-premises SSIS remains fully supported and is the right tool for environments that need to stay on-premises.
1 Repository Setup on the Ops Server
All collected health data lands in a central Ops database on one designated server. Create these objects once before building the SSIS package.
1.1 Server List
CREATE TABLE dbo.ServerList (
ServerListID INT IDENTITY(1,1) PRIMARY KEY,
ServerName SYSNAME NOT NULL,
AuthType VARCHAR(10) NOT NULL, -- 'Windows' or 'SQL'
LoginName SYSNAME NULL,
LoginSecret VARBINARY(256) NULL, -- optional: encrypted credential
IsEnabled BIT NOT NULL DEFAULT(1)
);1.2 Staging Tables
CREATE SCHEMA ops AUTHORIZATION dbo;
GO
-- SQL Agent job failures
CREATE TABLE ops.JobFailures (
SnapshotDT DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
ServerName SYSNAME NOT NULL,
JobName SYSNAME NOT NULL,
RunDate DATETIME NOT NULL,
Message NVARCHAR(4000) NULL
);
-- Error log findings (failed logins and high-severity events)
CREATE TABLE ops.ErrorLogFindings (
SnapshotDT DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
ServerName SYSNAME NOT NULL,
LogDate DATETIME NOT NULL,
Process NVARCHAR(64) NULL,
FindingType VARCHAR(20) NOT NULL, -- 'LOGIN_FAILED' or 'HIGH_SEVERITY'
Text NVARCHAR(4000) NOT NULL
);
-- Database and log file free space alerts
CREATE TABLE ops.FileFreePctAlerts (
SnapshotDT DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
ServerName SYSNAME NOT NULL,
DatabaseName SYSNAME NOT NULL,
FileType VARCHAR(10) NOT NULL, -- 'ROWS' or 'LOG'
LogicalName SYSNAME NOT NULL,
SizeMB DECIMAL(19,2) NOT NULL,
UsedMB DECIMAL(19,2) NOT NULL,
FreePct DECIMAL(5,2) NOT NULL
);1.3 Email Recipients
CREATE TABLE ops.ReportTargets (
ReportName SYSNAME PRIMARY KEY,
Recipients NVARCHAR(400) NOT NULL
);
INSERT INTO ops.ReportTargets VALUES
('JobFailures', 'dba@yourorg.com'),
('ErrorLog', 'dba@yourorg.com;secops@yourorg.com'),
('FileFree', 'dba@yourorg.com;storage@yourorg.com');1.4 HTML Encode Helper Function — Required
This function must be created before the email tasks will work. Job names, error messages, and server names can contain &, <, and > characters that break HTML email bodies if not escaped. It is not optional.
CREATE OR ALTER FUNCTION ops.fn_HtmlEncode (@s NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @s = REPLACE(@s, '&', '&');
SET @s = REPLACE(@s, '<', '<');
SET @s = REPLACE(@s, '>', '>');
SET @s = REPLACE(@s, '"', '"');
RETURN @s;
END;2 SSIS Package Blueprint
Package name: MultiServer_HealthSweep
2.1 Parameters (Package Level)
| Name | Type | Default | Purpose |
|---|---|---|---|
p_LookbackHours | Int32 | 24 | Lookback window for job failures and error log entries |
p_FreePctThreshold | Int32 | 10 | Free space percentage below which an alert is generated |
p_OpsConnection | Connection | — | Connection to the Ops database |
p_MailProfileName | String | OPS-DBMail | Database Mail profile name for outbound email |
Parameters vs Variables: Use package parameters if you deploy to the SSIS Catalog (recommended — parameters can be set per environment without editing the package). Use Variables if you are running from the file system. The tasks reference the same values either way.
2.2 Variables (Package Scope)
| Name | Type | Notes |
|---|---|---|
User::ServerList | Object | ADO recordset filled by the Load Server List task |
User::ServerName | String | Current server name — set by the Foreach loop |
User::AuthType | String | Windows or SQL — drives the connection string expression |
User::LoginName | String | SQL Auth login (blank for Windows Auth) |
User::LoginPassword | String (Sensitive) | SQL Auth password — stored as sensitive, never plain text in config |
2.3 Connection Managers
CM_Ops — static OLE DB connection to the Ops database. Set ServerName and InitialCatalog to your Ops server and database.
CM_Target — dynamic OLE DB connection. Set its ConnectionString property via an expression so it updates for each server in the loop:
-- Windows Auth expression:
"Data Source=" + @[User::ServerName]
+ ";Initial Catalog=master;Integrated Security=SSPI;"
-- SQL Auth expression (use when AuthType = 'SQL'):
"Data Source=" + @[User::ServerName]
+ ";Initial Catalog=master;User ID=" + @[User::LoginName]
+ ";Password=" + @[User::LoginPassword] + ";"SQL Auth credentials: Store the password in a sensitive SSIS variable or in the SSIS Catalog environment. Never store it in plain text in a configuration file. Use a SQL Agent proxy with a credential that has the minimum required permissions where possible.
2.4 Control Flow
The three email tasks run after the Foreach loop completes, not inside it. Each uses an IF EXISTS wrapper so mail is only sent when there is data to report.
3 Source Queries
Each Data Flow task has one OLE DB Source using CM_Target (the current server) and one OLE DB Destination using CM_Ops. The queries below go in the OLE DB Source SQL Command. SSIS maps positional ? markers to parameter values in order — the first ? maps to Parameter 0, the second to Parameter 1, and so on.
3.1 Job Failures (DF_JobFailures)
Parameters: Parameter 0 → p_LookbackHours, Parameter 1 → User::ServerName
-- OLE DB Source on CM_Target (target SQL Server)
-- Parameter 0 = p_LookbackHours (Int32)
-- Parameter 1 = User::ServerName (String)
DECLARE @dt DATETIME = DATEADD(HOUR, -?, SYSDATETIME()); -- param 0: lookback hours
SELECT
SYSDATETIME() AS SnapshotDT,
? AS ServerName, -- param 1
j.name AS JobName,
msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDate,
h.message AS Message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.run_status = 0 -- 0 = Failed
AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @dt
ORDER BY RunDate DESC;3.2 Error Log Findings (DF_ErrorLog)
This query makes two separate calls to sp_readerrorlog — one filtered to login failures, and one unfiltered to catch high-severity events. Reading the same temp table twice with different filters would never return high-severity rows because the initial read was already filtered to login failure text only.
Parameters: Parameter 0 → p_LookbackHours, Parameter 1 → User::ServerName, Parameter 2 → User::ServerName
-- OLE DB Source on CM_Target
-- Parameter 0 = p_LookbackHours (Int32)
-- Parameter 1 = User::ServerName (String) -- for login failed rows
-- Parameter 2 = User::ServerName (String) -- for high severity rows
DECLARE @dt DATETIME = DATEADD(HOUR, -?, SYSDATETIME()); -- param 0
-- Read 1: current error log filtered to 'Login failed'
CREATE TABLE #login_err (
LogDate DATETIME NOT NULL,
ProcessInfo NVARCHAR(64) NULL,
[Text] NVARCHAR(MAX) NOT NULL
);
INSERT INTO #login_err
EXEC master.sys.sp_readerrorlog 0, 1, 'Login failed';
-- Read 2: current error log unfiltered for high-severity scan
-- (Severity 16-25 messages do NOT contain the phrase "Login failed",
-- so a separate read is required -- a single filtered read misses them entirely)
CREATE TABLE #all_err (
LogDate DATETIME NOT NULL,
ProcessInfo NVARCHAR(64) NULL,
[Text] NVARCHAR(MAX) NOT NULL
);
INSERT INTO #all_err
EXEC master.sys.sp_readerrorlog 0, 1;
SELECT
SYSDATETIME() AS SnapshotDT,
? AS ServerName, -- param 1
LogDate,
ProcessInfo,
'LOGIN_FAILED' AS FindingType,
[Text]
FROM #login_err
WHERE LogDate >= @dt
UNION ALL
SELECT
SYSDATETIME(),
?, -- param 2
LogDate,
ProcessInfo,
'HIGH_SEVERITY' AS FindingType,
[Text]
FROM #all_err
WHERE LogDate >= @dt
AND (
[Text] LIKE '%Severity: 16%'
OR [Text] LIKE '%Severity: 17%'
OR [Text] LIKE '%Severity: 18%'
OR [Text] LIKE '%Severity: 19%'
OR [Text] LIKE '%Severity: 20%'
OR [Text] LIKE '%Severity: 21%'
OR [Text] LIKE '%Severity: 22%'
OR [Text] LIKE '%Severity: 23%'
OR [Text] LIKE '%Severity: 24%'
OR [Text] LIKE '%Severity: 25%'
)
DROP TABLE #login_err;
DROP TABLE #all_err;Why two reads? Login failure messages are logged as informational entries — they do not contain severity level text. High-severity errors (16–25) are engine-level events with completely different message formats. A single sp_readerrorlog call filtered to 'Login failed' will never return high-severity events, and a call filtered to 'Severity: 16' will never return login failures. The two-read approach captures both categories correctly with no duplicates between them.
3.3 File Free Space (DF_FileFreePct)
Covers both data files (via sys.database_files and FILEPROPERTY) and log files (via DBCC SQLPERF(LOGSPACE)). System databases are excluded from both sections for consistency — monitoring master, model, and msdb for free space is rarely actionable and creates noise. Remove the AND database_id > 4 filter if you want to include them.
Parameters: Parameter 0 → p_FreePctThreshold, Parameter 1 → User::ServerName, Parameter 2 → User::ServerName
-- OLE DB Source on CM_Target
-- Parameter 0 = p_FreePctThreshold (Int32)
-- Parameter 1 = User::ServerName (String) -- for data files
-- Parameter 2 = User::ServerName (String) -- for log files
DECLARE @Threshold DECIMAL(5,2) = ?; -- param 0
-- Data files: build dynamic SQL to query sys.database_files in each user database
DECLARE @dataResults TABLE (
DatabaseName SYSNAME NOT NULL,
LogicalName SYSNAME NOT NULL,
SizeMB DECIMAL(19,2) NOT NULL,
UsedMB DECIMAL(19,2) NOT NULL,
FreePct DECIMAL(5,2) NOT NULL
);
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
USE ' + QUOTENAME(name) + N';
SELECT
DB_NAME() AS DatabaseName,
df.name AS LogicalName,
CONVERT(DECIMAL(19,2), df.size / 128.0) AS SizeMB,
CONVERT(DECIMAL(19,2), FILEPROPERTY(df.name, ''SpaceUsed'') / 128.0) AS UsedMB,
CONVERT(DECIMAL(5,2),
(1.0 - FILEPROPERTY(df.name, ''SpaceUsed'') * 1.0 / df.size) * 100.0) AS FreePct
FROM sys.database_files AS df
WHERE df.type = 0 -- ROWS files only
AND df.state_desc = ''ONLINE'';
'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id > 4; -- exclude master, model, msdb, tempdb
INSERT INTO @dataResults
EXEC sp_executesql @sql;
-- Log files: DBCC SQLPERF(LOGSPACE) -- filter to user databases for consistency
CREATE TABLE #logspace (
DatabaseName SYSNAME NOT NULL,
LogSizeMB DECIMAL(19,2) NOT NULL,
LogUsedPct DECIMAL(5,2) NOT NULL,
Status INT NOT NULL
);
INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
SELECT
SYSDATETIME() AS SnapshotDT,
? AS ServerName, -- param 1
DatabaseName,
'ROWS' AS FileType,
LogicalName,
SizeMB,
UsedMB,
FreePct
FROM @dataResults
WHERE FreePct < @Threshold
UNION ALL
SELECT
SYSDATETIME(),
?, -- param 2
l.DatabaseName,
'LOG' AS FileType,
l.DatabaseName + N'_log' AS LogicalName,
l.LogSizeMB AS SizeMB,
l.LogSizeMB * l.LogUsedPct / 100.0 AS UsedMB,
100.0 - l.LogUsedPct AS FreePct
FROM #logspace l
-- Join to sys.databases to restrict to user databases (same filter as data files)
JOIN sys.databases d ON d.name = l.DatabaseName
WHERE d.database_id > 4
AND (100.0 - l.LogUsedPct) < @Threshold
ORDER BY DatabaseName, FileType;
DROP TABLE #logspace;4 Email Reports with sp_send_dbmail
The three email Execute SQL tasks run on CM_Ops after the Foreach loop completes. Each uses IF EXISTS so email is only sent when there is data to report. The tasks reference the ops.fn_HtmlEncode function created in Section 1.4 — that function must exist before these tasks will run.
4.1 Email_JobFailures
Parameter 0 → p_LookbackHours, Parameter 1 → p_MailProfileName
-- Execute SQL on CM_Ops
-- Parameter 0 = p_LookbackHours (Int32)
-- Parameter 1 = p_MailProfileName (String)
DECLARE @hrs INT = ?; -- param 0
DECLARE @prof SYSNAME = ?; -- param 1
DECLARE @html NVARCHAR(MAX);
DECLARE @subj NVARCHAR(255);
IF EXISTS (
SELECT 1 FROM ops.JobFailures
WHERE SnapshotDT >= DATEADD(HOUR, -@hrs, SYSDATETIME())
)
BEGIN
SET @subj = 'SQL Job Failures – Last ' + CONVERT(VARCHAR(10), @hrs) + ' Hours';
SET @html =
N'<h3>SQL Agent Job Failures (last ' + CONVERT(VARCHAR(10), @hrs) + N' hours)</h3>'
+ N'<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse;">'
+ N'<tr style="background:#f3f4f6;"><th>Server</th><th>Job</th>'
+ N'<th>Run Date</th><th>Message</th></tr>'
+ (
-- SQL Server 2017+: use STRING_AGG
-- For SQL Server 2016, see the FOR XML PATH alternative below
SELECT STRING_AGG(
N'<tr><td>' + ops.fn_HtmlEncode(ServerName) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(JobName) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(19), RunDate, 120) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(Message) + N'</td></tr>'
, N'')
FROM ops.JobFailures
WHERE SnapshotDT >= DATEADD(HOUR, -@hrs, SYSDATETIME())
)
+ N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @prof,
@recipients = (SELECT Recipients FROM ops.ReportTargets WHERE ReportName = 'JobFailures'),
@subject = @subj,
@body_format = 'HTML',
@body = @html;
ENDSQL Server 2016 compatibility — replace STRING_AGG with FOR XML PATH: The STRING_AGG function requires SQL Server 2017+. On SQL Server 2016, replace the STRING_AGG(...) block with:
-- FOR XML PATH alternative for SQL Server 2016:
(
SELECT
N'<tr><td>' + ops.fn_HtmlEncode(ServerName) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(JobName) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(19), RunDate, 120) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(Message) + N'</td></tr>'
FROM ops.JobFailures
WHERE SnapshotDT >= DATEADD(HOUR, -@hrs, SYSDATETIME())
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')4.2 Email_ErrorLog
Parameter 0 → p_LookbackHours, Parameter 1 → p_MailProfileName
-- Execute SQL on CM_Ops
DECLARE @hrs INT = ?;
DECLARE @prof SYSNAME = ?;
DECLARE @html NVARCHAR(MAX);
IF EXISTS (
SELECT 1 FROM ops.ErrorLogFindings
WHERE SnapshotDT >= DATEADD(HOUR, -@hrs, SYSDATETIME())
)
BEGIN
SET @html =
N'<h3>Error Log Findings (last ' + CONVERT(VARCHAR(10), @hrs) + N' hours)</h3>'
+ N'<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse;">'
+ N'<tr style="background:#f3f4f6;"><th>Server</th><th>Log Date</th>'
+ N'<th>Type</th><th>Process</th><th>Message</th></tr>'
+ (
SELECT STRING_AGG(
N'<tr><td>' + ops.fn_HtmlEncode(ServerName) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(19), LogDate, 120) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(FindingType) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(ISNULL(Process, '')) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(LEFT([Text], 500)) + N'</td></tr>'
, N'')
FROM ops.ErrorLogFindings
WHERE SnapshotDT >= DATEADD(HOUR, -@hrs, SYSDATETIME())
)
+ N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @prof,
@recipients = (SELECT Recipients FROM ops.ReportTargets WHERE ReportName = 'ErrorLog'),
@subject = N'SQL Error Log Findings – Last ' + CONVERT(VARCHAR(10), @hrs) + N' Hours',
@body_format = 'HTML',
@body = @html;
END4.3 Email_FileFree
Parameter 0 → p_FreePctThreshold, Parameter 1 → p_MailProfileName
-- Execute SQL on CM_Ops
DECLARE @pct INT = ?;
DECLARE @prof SYSNAME = ?;
DECLARE @html NVARCHAR(MAX);
IF EXISTS (SELECT 1 FROM ops.FileFreePctAlerts WHERE SnapshotDT >= CAST(GETDATE() AS DATE))
BEGIN
SET @html =
N'<h3>File Free Space Below ' + CONVERT(VARCHAR(10), @pct) + N'% Threshold</h3>'
+ N'<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse;">'
+ N'<tr style="background:#f3f4f6;"><th>Server</th><th>Database</th>'
+ N'<th>Type</th><th>File</th><th>Size MB</th>'
+ N'<th>Used MB</th><th>Free %</th></tr>'
+ (
SELECT STRING_AGG(
N'<tr><td>' + ops.fn_HtmlEncode(ServerName) + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(DatabaseName) + N'</td>'
+ N'<td>' + FileType + N'</td>'
+ N'<td>' + ops.fn_HtmlEncode(LogicalName) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(20), SizeMB) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(20), UsedMB) + N'</td>'
+ N'<td>' + CONVERT(VARCHAR(10), FreePct) + N'%</td></tr>'
, N'')
FROM ops.FileFreePctAlerts
WHERE SnapshotDT >= CAST(GETDATE() AS DATE)
)
+ N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @prof,
@recipients = (SELECT Recipients FROM ops.ReportTargets WHERE ReportName = 'FileFree'),
@subject = N'Database File Free Space Below ' + CONVERT(VARCHAR(10), @pct) + N'% Threshold',
@body_format = 'HTML',
@body = @html;
END5 Scheduling and Deployment
5.1 Deploy to SSIS Catalog (Recommended)
- In Visual Studio: right-click the project → Deploy → deploy to the SSIS Catalog on your Ops server
- Create environment variables for
p_LookbackHours,p_FreePctThreshold, andp_MailProfileNamein the SSIS Catalog environment - Map the package parameters to the environment variables so values can change without redeploying
5.2 SQL Agent Job
Create two SQL Agent jobs — one for the frequent health checks (job failures and error log) and one for the daily free space report:
- MultiServer_HealthSweep_Hourly — runs the full package every hour, 6 AM to 8 PM. Set
p_LookbackHours = 1so each run only collects the previous hour. - MultiServer_HealthSweep_Daily — runs once daily at 7 AM. Uses default parameters (24-hour lookback, 10% threshold). Sends the file free space report.
Alternatively run a single daily job at 6 AM with the defaults if hourly alerting is not required for your environment.
5.3 Optional: Run Log Table
-- Track package executions for auditing and troubleshooting
CREATE TABLE ops.RunLog (
RunID INT IDENTITY(1,1) PRIMARY KEY,
PackageName SYSNAME NOT NULL,
StartDT DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
EndDT DATETIME2 NULL,
Status VARCHAR(20) NOT NULL DEFAULT 'RUNNING', -- RUNNING, SUCCESS, FAILED
ErrorMessage NVARCHAR(MAX) NULL
);6 Troubleshooting Tips
sp_readerrorlogaccess denied. The login running the package needsVIEW SERVER STATEor membership insysadmin. As an alternative, create a lightweight SQL Agent job on each target server that copies filtered error log rows into a local table, then read that table from SSIS instead of callingsp_readerrorlogdirectly. This avoids the permission requirement and reduces network load.DBCC SQLPERF(LOGSPACE)fails. RequiresVIEW DATABASE STATEon all databases. Grant it at the server level withGRANT VIEW SERVER STATE TO [login].- Email not sending. Verify Database Mail is configured and the Agent account has
EXECUTEonmsdb.dbo.sp_send_dbmail. Check the Database Mail log:SELECT * FROM msdb.dbo.sysmail_log ORDER BY log_date DESC. - Package fails on one server but continues. Add a retry count of 2 and a retry interval of 30 seconds on the Sequence Container that wraps the per-server tasks. This handles transient network hiccups without failing the whole sweep.
- Very large error logs slow down
sp_readerrorlog. Cycle the error log more frequently (viaEXEC sp_cycle_errorlogin a maintenance job) to keep individual log files smaller. Or filter to a shorter lookback window on high-traffic servers. - HTML email shows raw
<characters instead of angle brackets. This meansops.fn_HtmlEncodewas not created or the@body_format = 'HTML'parameter is missing fromsp_send_dbmail. Verify both.
7 Build Order Summary
- Create the Ops database objects:
dbo.ServerList,opsschema, three staging tables,ops.ReportTargets, andops.fn_HtmlEncode - Populate
dbo.ServerListwith your target servers and setIsEnabled = 1 - Configure Database Mail on the Ops server and verify a test email sends successfully
- In SSIS: create the package, add parameters, set up CM_Ops and CM_Target
- Build the Load Server List Execute SQL task (Full resultset into
User::ServerList) - Add the Foreach ADO Enumerator loop with variable mappings
- Inside the loop: add DF_JobFailures, DF_ErrorLog, and DF_FileFreePct data flows using the source queries from Section 3
- After the loop: add Email_JobFailures, Email_ErrorLog, and Email_FileFree Execute SQL tasks using the email queries from Section 4
- Deploy to SSIS Catalog and create SQL Agent jobs for scheduling
- Run once manually, verify rows appear in the staging tables, and verify email is received
Once running, this package gives you a daily or hourly health snapshot across every SQL Server in your environment — job failures, error events, and storage pressure — with no manual checking required. Add servers to dbo.ServerList and they are automatically included on the next run.
References
- Microsoft Docs — SQL Server Integration Services (SSIS)
- Microsoft Docs — sp_readerrorlog
- Microsoft Docs — DBCC SQLPERF (LOGSPACE)
- Microsoft Docs — Database Mail
- Microsoft Docs — sp_send_dbmail
- Microsoft Docs — STRING_AGG (SQL Server 2017+)
- Microsoft Fabric — SSIS in Fabric (GA 2025)
- Microsoft Docs — SSIS Catalog
- SQLYARD — SQL Server DBA Health Check Toolkit
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
