Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

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

MSSQLTips author Basit Farooq By:   |   Read Comments (27)   |   Related Tips: More > 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) = '<YourEmailAddress@YourDomain.com>'
  
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


Last Update: 6/6/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, June 06, 2013 - 10:48:52 AM - kerany Read The Tip

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

 


Thursday, June 06, 2013 - 1:40:20 PM - John Wood Read The Tip

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 06, 2013 - 10:30:43 PM - Ben Hudson Read The Tip

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]


Friday, June 07, 2013 - 2:26:18 PM - Terese Read The Tip

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


Wednesday, July 10, 2013 - 8:07:35 AM - Sumera Sadath Read The Tip

It is of great help. Very good Article. Thanks 


Friday, August 23, 2013 - 9:05:08 AM - Reza Read The Tip

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

Kindest regards.

Reza


Friday, October 04, 2013 - 4:25:21 PM - Michele Copa Read The Tip

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


Monday, October 07, 2013 - 9:01:41 AM - Basit Farooq Read The Tip

Yes it does. 


Monday, October 07, 2013 - 4:28:12 PM - Michele Copa Read The Tip

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. 


Thursday, October 31, 2013 - 10:00:31 AM - vipul Read The Tip

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

 

 

 


Thursday, November 07, 2013 - 2:13:13 PM - Salman Read The Tip

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, November 07, 2013 - 4:34:32 PM - Mike Chubbs Read The Tip

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:


Monday, November 11, 2013 - 1:45:37 AM - vipul Read The Tip

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,

 

 


Wednesday, December 04, 2013 - 10:48:41 AM - Saumil Read The Tip

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


Thursday, January 16, 2014 - 1:34:43 PM - Harish Read The Tip

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,


Tuesday, January 21, 2014 - 5:19:10 PM - srivastk Read The Tip

 

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'. :-) 


Tuesday, February 25, 2014 - 4:54:59 AM - Praim Patar Read The Tip

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. webpatar@hotmail.com


Thursday, March 13, 2014 - 9:18:47 AM - John Waclawski Read The Tip

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, March 18, 2014 - 4:18:22 PM - aqueel Read The Tip

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


Tuesday, March 18, 2014 - 4:53:18 PM - aqueel Read The Tip

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


Thursday, March 20, 2014 - 9:11:05 AM - Harish Read The Tip

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,


Monday, May 12, 2014 - 2:13:42 PM - sreekanth Read The Tip

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]


Monday, May 12, 2014 - 2:16:44 PM - sreekanth Read The Tip

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


Monday, May 12, 2014 - 2:19:55 PM - sreekanth Read The Tip

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.


Thursday, May 15, 2014 - 11:29:42 AM - Rolson Read The Tip

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.


Thursday, May 15, 2014 - 12:16:28 PM - sri Read The Tip

 

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


Tuesday, June 10, 2014 - 10:04:33 PM - Carla Abanes Read The Tip

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.

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.