Centralized SSIS solution to monitor failed SQL Server Agent Jobs across the enterprise

By:   |   Comments (34)   |   Related: > SQL Server Agent


Problem

SQL Server Agent Jobs are crucial to any SQL Server environment as they are created and scheduled to perform critical business and operational tasks. As a database administrator (DBA) or developer, it is our responsibility to monitor SQL Server Agent Jobs to make sure they are running successfully, without any failures.  SQL Server provides several methods, such as job notifications, SQL Server agent alerts, etc., that can help monitor our SQL Server Agent Jobs, so that you can get an email or alert when a particular SQL Server agent job fails.  However, the problem with these monitoring solutions are that they are dependent on Database Mail, and if you are working for an organization where you are prohibited for enabling the Database Mail feature on a SQL Server instance, due to data security reasons, then the only option is to check the status of SQL Server Agent Jobs by manually reviewing the job status in Job Activity Monitor, which is a time consuming task, especially if you are managing a large number of SQL Servers.  In this tip, I'll show you how you can design your own centralized custom solution using SQL Server Integration Services that will email a report that contains the list of all SQL Server Agent Jobs across all of the SQL Servers that failed in the last hour.

Solution

Before I start talking about our custom solution, I will first discuss where SQL Server stores SQL Server Agent Job history information and show you different ways for viewing it in SQL Server.

Viewing SQL Server agent job history information

As we know, SQL Server Agent stores history information for jobs, alerts and operators in the msdb system database. The information about SQL Server Agent Job history is actually stored in the dbo.sysjobhistory table of the msdb database. You can view the history of SQL Server Agent Jobs and job steps through the Job Activity Monitor in SQL Server Management Studio or by directly querying dbo.sysjobhistory table. Here are the steps to launch the Job Activity Monitor page in SQL Server Management Studio:

  • In Object Explorer, expand SQL Server Agent.
  • Double-click Job Activity Monitor. The following summarizes information for all jobs:
    • Name
    • Enabled
    • Status
    • Last Run Outcome
    • Last Run (time)
    • Next Run
    • Category
    • Runnable
    • Scheduled
    • Category ID
  • To view information about individual job steps, right-click the job and choose View History. Detailed information about the outcome of each step is displayed in the Log File Viewer.
  • Click the Close X in the top right of the interface to close the Log File Viewer.
  • Click the Close X in the top right of the interface to close Job Activity Monitor.

Designing our custom solution for monitoring and alerting on failed SQL Server Agent Jobs across multiple SQL Servers

For the purpose of this tip, create a database called UtilityDB on your centralized SQL Server instance, where you want to save the failed SQL Server agent jobs information. I used the following Transact-SQL script to create this database:

CREATE DATABASE [UtilityDB]
 ON PRIMARY 
(NAME = N'UtilityDB'
, FILENAME = N'\UtilityDB.mdf' 
, SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
(NAME = N'UtilityDB_log'
, FILENAME = N'\UtilityDB_log.ldf' 
, SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Next, create the following two tables in the UtilityDB database:

  • SSISServersList: The table stores the name of each SQL Server you want to monitor for failed SQL Server Agent Job information.
  • SQLAgentFailedJobsInfo: The table will store the information about all SQL Server Agent Jobs that failed across all of the SQL Server instances.

To create the tables, execute the following Transact-SQL script:

USE [UtilityDB]
GO
CREATE TABLE [dbo].[SSISServersList](
 [ServerName]  [nvarchar](128) NOT NULL,
 [IsTest]  [bit] NULL,
 [Active]  [bit] NULL,
 [Port]  [int] NULL) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SQLAgentFailedJobsInfo](
 [SQLServer]   [nvarchar](128) NULL,
 [JobName]   [nvarchar](128) NOT NULL,
 [StepName]   [nvarchar](128) NOT NULL,
 [FailureMessage]  [nvarchar](4000) NULL,
 [LastRunDateTime]  [varchar](32) NULL,
 [LastRunDuration]  [varchar](32) NULL,
 [NextRunDateTime]  [varchar](32) NOT NULL) ON [PRIMARY]
GO

Now we are ready to design our SSIS package, to do that, launch SQL Server Data Tools, and create a new Integration Services project. Once the project is created, it will also create an empty package by default with the name Package.dtsx. Rename this package to CollectJobFailureInformation_Pkg.dtsx.

Next, follow the steps below to configure this SSIS package.

1: Define the SQL Server Integration Services Package Variables

Right-click anywhere on the empty panel on the Control Flow tab, then select Variables and then choose the Add Variables icon. Add the following two variables to the SSIS package:

  • Variable 1: SQLServer_Connection
    • Scope: CollectJobFailureInformation_Pkg
    • Data Type: String
    • Value:<Your SQL Server instance name> (Specify the instance name where the UtilityDB database is located)
  • Variable 2: SQLServer_ResultSet
    • Scope: CollectJobFailureInformation_Pkg
    • Data Type: Object
    • Value: System.Object
Define the SQL Server Integration Services Package Variables

2: Defining the SQL Server Integration Services Package Connections

First add an ADO.NET connection to the UtilityDB database on your SQL Server. To do this, click anywhere on the Connection Managers, and choose New ADO.NET Connection. Configure the ADO.NET connection as follows:

First add ADO.NET connection to the UtilityDB database on your SQL Server

Rename the ADO.NET connection as UtilityDB_ADOConnection.

Next, add the dynamic OLE-DB connection to the package, which allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains the SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_SQLServerConn.

To do so, right-click Dynamic_SQLServerConn OLE-DB connection and then choose Properties from menu. In the properties window, first change the initial catalog to master, and then click the ellipse  (...) box next to expressions property, and specify the following expression for ServerName property:

Rename the ADO.NET connection as UtilityDB_ADOConnection.

@[User::SQLServer_Connection]

3: Defining SQL Server Integration Services Package Tasks

3.1: Configuring "01 - EST Truncate Failed Jobs table in UtilityDB" - Execute SQL Task in SSIS

Add an "Execute SQL Task" to the Control Flow tab. Double-click the task and configure properties in the "General" page of the task as follows:

  • Rename "Execute SQL Task" to "01 - EST Truncate Failed Jobs table in UtilityDB".
  • Set "ResultSet" property to "None".
  • Set "Connection Type" property to "ADO.NET".
  • Set "Connection" property to "UtilityDB_ADOConnection".
  • Set "BypassPrepare" to "True".
  • Set "SQLStatement" property with the following:
TRUNCATE TABLE dbo.SQLAgentFailedJobsInfo

Add an

3.2: Configuring "02 - EST Get Server Names" - Execute SQL Task in the SSIS Package

Add another "Execute SQL Task" to the Control Flow tab. Double-click the task and configure properties in the "General" page of the task as follows:

  • Rename "Execute SQL Task" to "02 - EST Get SQL Server Names".
  • Set "ResultSet" property to "Full result set".
  • Set "Connection Type" property to "ADO.NET".
  • Set "Connection" property to "UtilityDB_ADOConnection".
  • Set "BypassPrepare" to "False".
  • Set "SQLStatement" to the following:
SELECT [ServerName] 
       + CASE ISNULL(CAST([Port] AS [varchar](10)), '')
           WHEN ''
           THEN ''
         ELSE ',' + CAST([Port] AS [varchar](10))
         END AS [ServerName]
      FROM [dbo].[SSISServersList]
      WHERE [Active] = 1
GO

Configuring

Click the ResultSet option in the left pane and then click the Add button on the bottom right of the interface to add the "User::SQLServer_ResultSet" variable as shown below. Change the Result Name value to 0, and then click OK to save the configuration for the task.

add the "User::SQLServer_ResultSet"

Now connect "01 - EST Truncate Failed Jobs table in UtilityDB" task to the "02 - EST Get SQL Server Names" task.

3.3: Configuring "03 -Load SQL Server Agent Failed Job Information" - Foreach Loop container in SSIS

Add a Foreach Loop container from the SSIS toolbox to the Control Flow tab. Double-click the Foreach Loop container and rename the Foreach Loop container to "03 -Load SQL Server Agent Failed Job Information". Once done, click the Collection option in the left pane and configure the properties of this window as follows:

  • Change "Enumerator" property to "Foreach ADO Enumerator".
  • Set "Enumeration Mode" to "Rows in the first table".
  • Specify "ADO object source variable" to the "User::SQLServer_ResultSet" package variable.
Configuring

Now click "Variable Mappings" and select "User::SQLServer_Connection" package variable to map to the collection value.

Now click

Then in the SSIS package, connect the "02 - EST Get SQL Server Names" task with the "03 - Load SQL Server Agent Failed Job Information" Foreach container.

3.4: Configuring "10 - EST Get Failed Jobs List" - Execute SQL Task in the SSIS Package

Add an "Execute SQL Task" inside the ForEach loop container. Double-click the task and configure the properties in the "General" page of the task as follows:

  • Rename "Execute SQL Task" to "10 - EST Get Failed Jobs List".
  • Set "ResultSet" property to "None".
  • Set "Connection Type" property to "OLE DB".
  • Set "Connection" property to "Dynamic_SQLServerConn".
  • Set "BypassPrepare" to "True".

Set the "SQLStatement" property to the script below, which gets the lists of all jobs that failed on the SQL Server instance:

SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM [tempdb].[sys].[objects] 
    WHERE [name] = 'SQL_AGENT_FAILED_JOBS' AND [type] IN (N'U'))
BEGIN
CREATE TABLE [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS](
 [SQLServer]  [nvarchar](128) NULL,
 [JobName]  [nvarchar](128) NOT NULL,
 [StepName]  [nvarchar](128) NOT NULL,
 [FailureMessage] [nvarchar](4000) NULL,
 [LastRunDateTime] [varchar](32) NULL,
 [LastRunDuration] [varchar](32) NULL,
 [NextRunDateTime] [varchar](32) NOT NULL)
END
ELSE 
BEGIN
 TRUNCATE TABLE [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
END;
WITH [FailedJobs]
AS 
(SELECT DISTINCT 
  CAST(SERVERPROPERTY('ServerName') AS [nvarchar](128)) AS [SQLServer]
 ,j.[name] AS [JobName]
 ,j.[job_id] AS [JobID]
 ,[sjh].[step_name] AS [StepName]
 ,[sjh].[message] AS [FailureMessage]
 ,LEFT(CAST(sjh.run_date AS [char](10)),4) 
 + '-' + SUBSTRING(CAST(sjh.[run_date] AS [char](10)),5,2)
 + '-' + SUBSTRING(CAST(sjh.[run_date] AS [char](10)),7,2) 
 + ' ' + SUBSTRING (right (STUFF (' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6), 1, 2)
 + ':' + SUBSTRING (right (STUFF (' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6) ,3 ,2)
 + ':' + SUBSTRING (right (STUFF (' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6) ,5 ,2) as [LastRunDateTime]
,(CASE 
   WHEN RIGHT('000000' + CONVERT([varchar](6), [sjh].[run_duration]), 6) = '000000'
 THEN '__:__:__'
   WHEN LEFT(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 4) = '0000'
    THEN '__:__:' + RIGHT(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
   WHEN LEFT(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2) = '00'
    THEN '__:' + SUBSTRING(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 3, 2) 
 + ':' + RIGHT(RIGHT('000000' + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
  ELSE LEFT(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2) 
  + ':' + SUBSTRING(RIGHT('000000' 
 + CONVERT([varchar](6), [sjh].[run_duration]), 6), 3, 2) 
 + ':' + RIGHT(RIGHT('000000' 
  + CONVERT([varchar](6), [sjh].[run_duration]), 6), 2)
 END) AS [LastRunDuration]
FROM [msdb].[dbo].[sysjobhistory] sjh
INNER JOIN [msdb].[dbo].[sysjobs] j ON [sjh].[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s ON j.[job_id] = s.[job_id]
 AND [sjh].[step_id] = s.[step_id]
WHERE [sjh].[run_status] = 0
 AND [sjh].[run_status] = 0
 AND left(CAST([sjh].[run_date] AS [char](10)), 4) 
  + '-' + SUBSTRING(CAST([sjh].[run_date] AS [char](10)), 5, 2) 
  + '-' + SUBSTRING(CAST([sjh].[run_date] AS [char](10)), 7, 2) 
  + ' ' + SUBSTRING(right(STUFF(' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6), 1, 2) 
  + ':' + SUBSTRING(right(STUFF(' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6), 3, 2) 
  + ':' + SUBSTRING(right(STUFF(' ', 1, 1, '000000') 
  + CONVERT([varchar](6), [sjh].[run_time]), 6), 5, 2) >= CONVERT(CHAR(19)
   ,(SELECT dateadd(hour, (- 1), CURRENT_TIMESTAMP)), 121))
INSERT INTO [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
           ([SQLServer]
           ,[JobName]
           ,[StepName]
           ,[FailureMessage]
           ,[LastRunDateTime]
           ,[LastRunDuration]
           ,[NextRunDateTime])
SELECT cte.[SQLServer]
   ,cte.[JobName]
   ,cte.[StepName]
   ,cte.[FailureMessage]
   ,cte.[LastRunDateTime]
   ,cte.[LastRunDuration]
   ,ISNULL (CONVERT ([varchar] (19), sch.[NextRunDateTime], 120),'___________________') 
FROM [FailedJobs] cte
 LEFT JOIN
  (SELECT SJS.job_id
 ,MIN(CONVERT(DATETIME, CONVERT([varchar](8), sjs.[next_run_date]) 
  + ' ' + LEFT(RIGHT('000000' 
  + CONVERT([varchar](6), sjs.[next_run_date]), 6), 2) 
  + ':' + SUBSTRING(RIGHT('000000' 
  + CONVERT([varchar](6), sjs.[next_run_date]), 6), 3, 2) 
  + ':' + RIGHT(RIGHT('000000' 
  + CONVERT([varchar](6), sjs.[next_run_time]), 6), 2))) AS [NextRunDateTime]
   FROM [msdb].[dbo].[sysjobschedules] sjs
   WHERE sjs.[next_run_date] > 0
   GROUP BY sjs.[job_id]) sch 
 ON sch.[job_id] = cte.[JobID]
   
SET NOCOUNT OFF;
GO

Configuring

3.5: Configuring "20 - DFT Copy Results to Failed Jobs Table in UtilityDB" - Data Flow Task in SSIS

Add a "Data Flow Task" inside the ForEach loop container and rename it to "20 - DFT Copy Results to Failed Jobs Table in UtilityDB". Connect the "Data Flow Task" with the "10 - EST Get Failed Jobs List". Next, double-click the "Data Flow Task", and drag the "OLE DB Source" and "ADO NET Destination" to the "Data Flow Task". Then, connect the "OLE DB Source" with the "ADO NET Destination. Now, double-click "OLE DB Source", and configure it as follows:

  • Set "OLE DB Connection Manager" to "Dynamic_SQLServerConn".
  • Change "Data access mode" to "SQL command"
  • Specify the following query in "SQL command text":
SELECT [SQLServer]
      ,[JobName]
      ,[StepName]
      ,[FailureMessage]
      ,MAX([LastRunDateTime]) AS [LastRunDateTime]
      ,[LastRunDuration]
      ,[NextRunDateTime]
FROM [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
GROUP BY [SQLServer]
        ,[JobName]
        ,[StepName]
        ,[FailureMessage]
        ,[LastRunDuration]
        ,[NextRunDateTime]
GO

Once done, click OK to save the settings and return to Data Flow.

Once done, click OK to save the settings. 

Finally, double-click "ADO NET Destination" and configure as follow:

  • Set "Connection manager" to "UtilityDB_ADOConnection".
  • Select "dbo.SQLAgentFailedJobsInfo" table in our "UtilityDB" database, where data will be copied.
  • Then map source table to destination table. 
double-click

Set

Click OK to save settings and return to the control flow tab.

3.6: Configuring "04 - EST Job Failure Report" - Execute SQL Task

Add an "Execute SQL Task" to the control flow tab. Double-click the task and configure properties in the "General" page of the task as follows:

  • Rename "Execute SQL Task" to "04 - EST Job Failure Report".
  • Set "ResultSet" property to "None".
  • Set "Connection Type" property to "ADO.NET".
  • Set "Connection" property to "UtilityDB_ADOConnection".
  • Set "BypassPrepare" to "True".

Set "SQLStatement" property with the script below, which emails the job failure report for all SQL Server instances to desired email address:

  
DECLARE @EmailRecipients [nvarchar] (128) = '<[email protected]>'
  
SET NOCOUNT ON 
SET ANSI_WARNINGS OFF
  
DECLARE @vXML_String [nvarchar](max) 
       ,@vBody [nvarchar](max)  
       ,@Subject [varchar](256)  
       ,@Xml [xml]  
    
SET @vBody = ''
  
SET @vXML_String = 
CONVERT ([nvarchar] (MAX), 
(SELECT '',j.[SQLServer] AS [td]  
      ,'',j.[JobName] AS [td]  
      ,'',j.[StepName] AS [td]  
      ,'',j.[FailureMessage] AS [td]  
      ,'',j.[LastRunDateTime] AS [td]  
      ,'',j.[LastRunDuration] AS [td]  
      ,'',j.[NextRunDateTime] AS [td]  
FROM [UtilityDB].[dbo].[SQLAgentFailedJobsInfo] j 
ORDER BY j.[SQLServer] 
FOR XML PATH ('tr')))
  
IF @vXML_String IS NOT NULL
  
BEGIN
  
SET @vBody = @vBody +
  
'<left><font face="Arial" size="2"> > The following are the list of servers where 
  
SQL Agent jobs failed in the last hour: </font><br /></left>
  
<left> 
<table border="1" cellpadding="2" width="1100"> 
<tr> 
 <th width="10%" align="left">SQL Server</th>  
 <th width="18%" align="left">Job Name</th>  
 <th width="15%" align="left">Step Name</th>  
 <th width="25%" align="left">Failure Message</th>  
 <th width="12%" align="left">Latest Run <br />(Date & Time)</th>  
 <th width="5%" align="left">Duration</th>  
 <th width="15%" align="left">Next Scheduled Run <br />(Date & Time)</th>  
</tr>'
  
SET @vBody = @vBody+@vXML_String+
  
' </table>
  
</left>'
  
END
  
IF @vBody <> ''
  
BEGIN
  
SET @vBody = ' 
<html>  
<body>  
<style type="text/css">
  
table {  
font:8pt tahoma,arial,sans-serif;  
}  
th {  
color:#FFFFFF; 
font:bold 8pt tahoma,arial,sans-serif; 
background-color:#204c7d; 
padding-left:5px;  
padding-right:5px;  
}  
td { 
color:#000000; 
font:8pt tahoma,arial,sans-serif; 
border:1px solid #DCDCDC;  
border-collapse:collapse;  
padding-left:3px;  
padding-right:3px;  
}  
.Warning {  
background-color:#FFFF00;   
color:#2E2E2E;  
}  
.Critical {  
background-color:#FF0000;  
color:#FFFFFF;  
}  
.Healthy {  
background-color:#458B00;  
color:#FFFFFF;  
}  
h1 { 
color:#FFFFFF; 
font:bold 16pt arial,sans-serif; 
background-color:#204c7d;  
text-align:center;  
}  
h2 {  
color:#204c7d;  
font:bold 14pt arial,sans-serif;  
}  
h3 {  
color:#204c7d;  
font:bold 12pt arial,sans-serif;  
}
  
body { 
color:#000000;
font:8pt tahoma,arial,sans-serif; 
margin:0px;  
padding:0px;  
}
  
</style>' 
+ '<h2> > SQL Agent Job Failure Alert </h2>' + 
+ @vBody +  
'
  
</body>  
</html>  
'
  
SET @vBody = REPLACE (@vBody,'<td>right_align','<td align="right">')
  
SET @subject =  'SQL Agent Job Failure Alert. Sent Time: ' 
  + CAST(GETDATE() AS VARCHAR(20))
   
EXEC msdb.dbo.sp_send_dbmail  
  @profile_name  = '<Specify_DatabaseMail_Profile_Name>'   
 ,@recipients  = @EmailRecipients  
 ,@body  = @vBody  
 ,@subject  = @subject  
 ,@body_format  = 'HTML'  
END  
GO

Once done, click OK to close script task, and then save the SSIS package

Once done, click OK to close script task, and then save the SSIS package.

Package Review

All done, our package is fully configured, and it is ready for you to execute the package. The package control flow and data flow look similar to the following figures:

Control Flow - CollectJobFailureInformation_Pkg.dtsx

Control Flow - CollectJobFailureInformation_Pkg.dtsx

Data Flow - CollectJobFailureInformation_Pkg.dtsx

Data Flow - CollectJobFailureInformation_Pkg.dtsx

Testing

To test the package, simply add a few servers in the SSISServerList table and then execute the package using the play button in SQL Server Data Tools (see below):

simply add a few servers in the SSISServerList table and then execute the package using the play button in SQL Server Data Tools

Upon successful execution, the package will send you an email containing the list of servers where SQL Agent jobs failed in the last hour (see below):

the package will send you an email containing the list of servers where SQL Agent jobs failed in the last hour
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Basit Farooq Basit Farooq is a Senior Database Administrator and has worked in the IT industry for 11+ years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, July 30, 2018 - 5:11:49 PM - Harvie Fingers Back To Top (76906)

 Hello, the package works great and I love the article but I do not get the email. Any help would be greatly appreciated.

Thursday, December 24, 2015 - 5:41:44 AM - krishnam Back To Top (40306)

 I have configured everything but its failing at "10 - EST Get Failed Jobs List" and below is the error

[Connection manager "Dynamic_SQLServerConn"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".

 

Can anyone help me to resolve this issue.

Wednesday, September 9, 2015 - 7:29:05 AM - Rizwan Back To Top (38621)

 

Thank you very much!

I am stuck in 02 - EST Get server Names.

I have no much idea about what server details i have to add in the table, so i have included only instance name and made it as active.

Request you to please advice.

 

Error:

 

[Execute Sql task]Error : Executing the query "Select [ServerName] + CASEISNULL(CASE([PO..."failed with the following error:

"The type of the value (DBNull) being assigned to variable "User::SQLServer_Connection" differs from the current variable

Task02 - ESST GET server Names failed.

 

 

 

 

Thursday, August 6, 2015 - 12:04:31 PM - Roni Vered Back To Top (38396)

Thanks for the post, Awosome solution.

We have a large environment and centralizing the failed job was one of my tasks.

 

Thursday, March 12, 2015 - 9:23:06 AM - Bill Ross Back To Top (36508)

I recently implemented a third party product called "BI xPress" (the free version) from http://pragmaticworks.com to simply logging of failed SSIS packages. The utility adds several event handlers that log failures and warnings as well as execution events. One can also manually write to the database as well. There are built in reports but in my case I wrote a job to poll the database and open a help desk ticket for any errrors. The help desk system then sends an email.

 

Wednesday, December 3, 2014 - 1:38:57 AM - Anil Back To Top (35471)

Thanks Basit for a well documented and detailed article and the code. This is awesome and is helping me monitor my servers on a hourly basis. Thanks for sharing!

 

Anil

Thursday, October 9, 2014 - 8:24:42 PM - brainee28 Back To Top (34915)

Thank you for posting this. It has been very helpful for us. 

I am running into one issue however. Some of our servers are SQL 2000 servers (yes I know, they are old and need to be changed out) but the trouble is that in section 3.4, your script uses a WITH statement, and because of this, won't work with any of our 2000 systems.

Is there a way I can get this working for 2000? An alteration to the script perhaps?

Tuesday, June 10, 2014 - 10:04:33 PM - Carla Abanes Back To Top (32183)

Awesome post! I followed all the steps and everything is working perfect. Just additional tip though. If you have problem setting SQL Server Data Tool, like i did, try to run rootsupd.exe in your system. I have Win 7 in my pc and i did had problem setting up the SQL Server Data Tool due to certiifcate issue.

Other than that, everything was smooth.

 

Thursday, May 15, 2014 - 12:16:28 PM - sri Back To Top (30795)

 

Excellent Article; I wanted to know for testing instead of dynamicconnection, I want to test only to server,

how do I do.

thanks in advance

 

sri

Thursday, May 15, 2014 - 11:29:42 AM - Rolson Back To Top (30794)

Great article. It is easy to follow. Could someone tell me how to log the failed connect to a table, so we can keep track the trouble SQL instance.

Monday, May 12, 2014 - 2:19:55 PM - sreekanth Back To Top (30741)

John Waclawski - Once you created the Execute Task, Right click on it and go to Properties and you should be able to change it as needed.

Monday, May 12, 2014 - 2:16:44 PM - sreekanth Back To Top (30740)

Terese - Make sure the active column in SSISServerslist table is set to 1.

Monday, May 12, 2014 - 2:13:42 PM - sreekanth Back To Top (30739)

Harish - To make it one day, change the dateadd part from 1 to 24...as shown below.

CONVERT

 

([varchar](6), [sjh].[run_time]), 6), 5, 2)>=CONVERT(CHAR(19

)

,(SELECTdateadd(hour,(- 24), CURRENT_TIMESTAMP)), 121

))

INSERT

 

INTO [tempdb].[dbo].

[SQL_AGENT_FAILED_JOBS]

Thursday, March 20, 2014 - 9:11:05 AM - Harish Back To Top (29822)

Hi,

I've deplolyed the SSIS package and scheduled it on daily basi, For me the packge is failing whenever there is Server reboots. The error is regarding Temp table that was created on all servers. As server gets rebootd the Temp table will be dropped and never created with SSIS package. If I manually create the Temp table and run it, the scheduled package runs fine.

Any thoughts.

Thanks,

Tuesday, March 18, 2014 - 4:53:18 PM - aqueel Back To Top (29805)

as an addition to previous comment, my ole DB source and ado net destination objects in data flow both have red "X" marks on the them. something is missing or wrong in here I think. i've went over instructions three times with no luck

Tuesday, March 18, 2014 - 4:18:22 PM - aqueel Back To Top (29804)

getting an error

"the variable "user::sqlserver_connection" was not found in the variables collection"

someone in comments wrote about this issue but i don't understand their explanation. please hlep

Thursday, March 13, 2014 - 9:18:47 AM - John Waclawski Back To Top (29740)

On Step "02 - EST Get Server Names" I was not able to set the "BypassPrepare" to "False".  That option is greyed out.  I'm continuing on with the set up but not sure if this will affect the outcome.  How do I get that set to "False"??

 

 

Tuesday, February 25, 2014 - 4:54:59 AM - Praim Patar Back To Top (29563)

Great article but I am seeing gibberish alphabets all over in the article like the ones shown below:

3.6: Configuring “04 - EST Job Failure Report � – Execute SQL Task

May be need adjustment to internet settings? appreciate help. [email protected]

Tuesday, January 21, 2014 - 5:19:10 PM - srivastk Back To Top (28165)

 

I just want to clarify one thing that is not that clear in this article. I finally got the Dynamic_SQlServerConn working and loop through the Servers.

To create the Dynamic_SQLServerConn - You have to create a new OLE DB connection. Rename that connection to Dynamic_SQLServerConn. Right click on it and click properties and then change the initial catalog to master and then expand Expressions and enter the value  'Servername' under Property, then '@[User::SQLServer_Connection]' under Expression. Hope this clarifies the other users who have the same problem. Since, I was running into the same issue - couldnt find a 'dynamic Ole DB connection in the toolbox'. :-) 

Thursday, January 16, 2014 - 1:34:43 PM - Harish Back To Top (28104)

Great Article, the query is checking for only last hour, can we make this to last 1 day(so that I can schedule the SSIS package every day and look for the failed jobs)

Thanks,

Wednesday, December 4, 2013 - 10:48:41 AM - Saumil Back To Top (27687)

Great Article! Very well written and nicely explained. I used the solution  and developed a solution for our environment with some tweaks. I am creating temp table in a monitoring db instead of tempdb as the tempdb gets flushed after monthly patch reboots.

One quick question here.. If I were to conditionally color code the HTML report, how would i achieve that? 

 

-Thanks

Monday, November 11, 2013 - 1:45:37 AM - vipul Back To Top (27457)

How to add the dynamic OLE-DB connection to the package ?

 

I am anable to find dynamic OLE-DB connection .

I am using SQL server 2008 R2 and vss 2008

 

Regards,

 

 

Thursday, November 7, 2013 - 4:34:32 PM - Mike Chubbs Back To Top (27442)

Great article but I cannot find how to add dynamic OLE-DB connection. There is no Dynamic connection in the OLE list.

Next, add the dynamic OLE-DB connection to the package, which allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains the SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_SQLServerConn.

To do so, right-click Dynamic_SQLServerConn OLE-DB connection and then choose Properties from menu. In the properties window, first change the initial catalog to master, and then click the ellipse  (…) box next to expressions property, and specify the following expression for ServerName property:

Thursday, November 7, 2013 - 2:13:13 PM - Salman Back To Top (27441)

Very nice and very well written article.

I have a question --- In my report the last column (Next Schedule Run) is not getting populated and my test job which failed is scheduled to run every day at 12:00 AM.

I tried to troubleshoot but not able to figure it out where I am wrong.

Thursday, October 31, 2013 - 10:00:31 AM - vipul Back To Top (27351)

Hello Sir ,

 

How to add the dynamic OLE-DB connection to the package, which allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains the SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_SQLServerConn.

I am using SQL server 2008 R2 and vss 2008

 

I am unable to add the dynamic connection .

 

Please Help me

 

Thanks

 

 

 

Monday, October 7, 2013 - 4:28:12 PM - Michele Copa Back To Top (27066)

Thanks - I actually tried it out earlier today and discovered that it works beautifully.  One thing I was wondering though is in reference to the next scheduled run date & time.  In both my run and in the screen shot you display above, the date is there but the time is always 00:00:00 - although the latest run date and time does show the time correctly. 

Monday, October 7, 2013 - 9:01:41 AM - Basit Farooq Back To Top (27060)

Yes it does. 

Friday, October 4, 2013 - 4:25:21 PM - Michele Copa Back To Top (27042)

Will this check job status on 2008r2 and 2005 instances as well?

Friday, August 23, 2013 - 9:05:08 AM - Reza Back To Top (26454)

Great package my friend , any chance you can save as VS 2008 r2 version please ?

Kindest regards.

Reza

Wednesday, July 10, 2013 - 8:07:35 AM - Sumera Sadath Back To Top (25774)

It is of great help. Very good Article. Thanks 

Friday, June 7, 2013 - 2:26:18 PM - Terese Back To Top (25344)

I tried this package, and all ran successfully.  I did not however get the email.  Any suggestions on where I went wrong?

Thursday, June 6, 2013 - 10:30:43 PM - Ben Hudson Back To Top (25326)

Great Article!

Just created this across multiple servers and it works a treat. One issue however in step 3.5. There needs to be a parameter based WHERE clause in the Select Statement, otherwise you'll get duplicate records based on the number of rows you have in the 'SSISServerList' Table.

Set the parameter to equal the SQLServer_Connection variable. Then change the SQL to include the WHERE clause.

SELECT [SQLServer]
      ,[JobName]
      ,[StepName]
      ,[FailureMessage]
      ,MAX([LastRunDateTime]) AS [LastRunDateTime]
      ,[LastRunDuration]
      ,[NextRunDateTime]
FROM [tempdb].[dbo].[SQL_AGENT_FAILED_JOBS]
WHERE SQLSERVER = ?
GROUP BY [SQLServer]
        ,[JobName]
        ,[StepName]
        ,[FailureMessage]
        ,[LastRunDuration]
        ,[NextRunDateTime]

Thursday, June 6, 2013 - 1:40:20 PM - John Wood Back To Top (25324)

Thank you for sharing a truly fine article with the community. This is indeed very helpful and easy to implement following your precise instructions. However, I would like to point out just one area of possible confusion especially for anyone not as familar with SSIS. This has to do with Mappings in step 3.5. If the script from 3.4 is not run prior to creating the data-flow, there are no Source fields to map. This is because the [tempdb] table is not yet created. I had to run the 3.4 script from within SSMS in order to have the table available for mapping in step 3.5 Just another nit-pick is the [tempdb] table is never removed but persists across sessions.

Thursday, June 6, 2013 - 10:48:52 AM - kerany Back To Top (25322)

thanks for this great tip, I'will deploy this solution in the company where I'm working

 














get free sql tips
agree to terms