We talked
lots about SQL Server, Now moving to post this article of windows combining with SQL. You all know we can schedule
ssis packages with scheduled jobs in SQL Server and with windows task scheduler too. We can easily get information and statistics of scheduled jobs which
I ported just recently. We should have
the same statistics for the windows task scheduled like a scheduled
job from SQL Server and we should have an alert for
its failures.
In this article i am creating one ssis package to achieve the same thing which we discussed above. So let us start and first creating prerequisite objects needed in ssis package.
1. Creating staging table where we will import all windows scheduled tasks statistics.
CREATE TABLE [dbo].[WindowsTasksStatus]
(
[seq] [int] IDENTITY(1,1) NOT NULL,
[HostName] [varchar](200) NULL,
[TaskName] [varchar](1000) NULL,
[NextRunTime] [varchar](100) NULL,
[Status] [varchar](100) NULL,
[LastRunTime] [varchar](100) NULL,
[LastResult] [varchar](100) NULL,
[Schedule] [varchar](4000) NULL,
[TaskToRun] [varchar](100) NULL
)
2. Create a batch file named WindowsTaskStatus.bat with following code. Pass server name in place of <
HostName> and provide a path where to export csv file.
SCHTASKS /Query /S HostName /FO csv /V > E:\WindowsTaskStatus\hhax4_WindowsTaskStatus.csv
After creating all
prerequisites, create a ssis package as per image and the all steps following,
Let me elaborate each task used in above ssis package.
Step 1 : Execute SQL TaskIn
the first step, we will truncate
the table on every execution of this package, so this table have following code with connection of
SQL server database where we imports staging data.
Truncate table dbo.WindowsTasksStatus
Step 2 : Execute Process TaskIn this step, we will call batch files created above.
Step 3 : For Each Loop Container We will each files created in
the second step and pass file name in variable, We have used For Each Loop Container because we can schedule other servers in
the same batch file to export windows task status.
Step 4 : Data Flow Task This task will pickup file passed from variable and import file data (Flat File Source) into
a table (OLE DB Destination) which we created earlier. We also provide expression of connection string as FileName through property of
the connection created for Flat File Source.
Step 5 : File System TaskAfter processing the files, it will be deleted by this task.
Step 6 : Execute SQL TaskNow we have staging table and also data imported from
csv files for windows tasks statistics. So this final step to get
the status of windows
task's status. Here we will fetch the data from table for those windows tasks which have
a last run status is not successful with last failed
date time, next run time and excluding tasks which are currently executing. Following is the code used in this step which will fire an alert through an email.
DECLARE @Subject VARCHAR (1000)
DECLARE @Body VARCHAR(max)
DECLARE @hpart VARCHAR(400)
DECLARE @MailProfile VARCHAR(50)
-- Use your mail profile here
SET @MailProfile = '<Mail Profile>'
CREATE TABLE #failedtasks
(
hostname VARCHAR(100),
taskname VARCHAR(100),
lastruntime VARCHAR(100),
nextruntime VARCHAR(100)
)
SET @Subject = 'Failed windows tasks alert'
-- Preparing column header
SET @hpart= '<tr><td>HostName</td><td>TaskName</td><td>LastRunTime</td><td>NextRunTime</td></tr>'
; WITH ctetasks
AS (SELECT hostname,
taskname,
lastruntime,
Cast (Substring(lastruntime, Charindex(',', lastruntime)+1, Len(
lastruntime))
+ ' '
+ Substring(lastruntime, 0, Charindex(',', lastruntime))
AS
DATETIME) AS
LastRunDateTime,
nextruntime
FROM dbo.windowstasksstatus
WHERE lastresult <> 0
-- Excuding task with disabled status
AND nextruntime <> 'Disabled'
AND lastruntime <> 'Disabled'
AND lastruntime <> 'N/A'
AND nextruntime <> 'N/A'
AND hostname <> 'HostName'
-- Excuding task which are running
AND status <> 'Running'
)
-- Importing above result in temp table
INSERT INTO #failedtasks
SELECT hostname,
taskname,
lastruntime,
nextruntime
FROM ctetasks
WHERE Cast(lastrundatetime AS DATETIME) >= Dateadd(dd, 0, Datediff(dd, 0,
Getdate()))
-- creating a mail body from temp table data
SET @Body = Cast ( ( SELECT td = hostname, '', td = taskname, '', td =
lastruntime, '', td =
nextruntime FROM #failedtasks FOR xml path('tr'), type ) AS VARCHAR(
max) ) + N''
SET @Body = '<table border="1">' + @hpart + @Body
+ '</table>' + '<br />'
-- Firing an email
IF ( @Body IS NOT NULL )
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@from_address = 'Windows Tasks Alert <alert@hhaexchange.com>',
@recipients = 'prajapatipareshm@gmail.com',
@subject = @subject,
@body = @Body,
@profile_name = @MailProfile,
@body_format = 'HTML';
END
-- Dropping temp table
DROP TABLE #failedtasks
You may be
using this or any other way to get windows scheduled tasks statistics, Please share your valuable inputs here.
