SSIS package to get windows scheduled task status by serverku

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 Task
In 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 Task
In 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 Task
After processing the files, it will be deleted by this task.

Step 6 : Execute SQL Task
Now 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.