How to add condition for an sql task

Question

Post reply

How to add condition for an sql task

rds207

Old Hand

Points: 344
More actions
March 26, 2010 at 7:07 pm

#219916

Hi
I am creating an SSIS package which gets data from msdb.dbo.sysjobhistory to a temp table where
run_duration is > 18000(which is 5hrs), when the data greater than run_duration > 18000 enters
into temp table i have created an identity row_num in temp table which increases by 1,
So i have created a stored procedure which generated an email to notify with an alert saying the Job has hanged for more than 5hrs,
Here is the structure of my SSIS Packages
1.First package gets data from sysjobhistory table , load into temp table
2.Need to write a condition if row_num > 1 to
execute the sql task to run the store procedure which sends an email,
So i have created stored procedure but dont understand where to give the condition if row_num is > 1 ..
Another Option I have tried is by creating a Job and writing the T -SQL Code as below, but cannot understand where to give the condition?
SELECT * FROM sysjobhistory
WHERE run_duration >= '18000'
and run_date = (convert(varchar,getdate(),112))-2
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'qctreport',
@recipients = 'c_rsrira@qualcomm.com',
@subject=N'ALERT :: Job Hanged for More than 5hrs.....' ;
END
GO
Could anybody please help...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

There was an SSC article about this just a few days ago, which you might find helpful:

http://www.sqlservercentral.com/articles/Monitoring/69649/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rds207 Old Hand Points: 344 More actions · Answer 2

can anybody please help ne writing the condition here ,

the procedure given in link is such a big one and i dont have permissions for doing so...

Nawar.Tabaa Ten Centuries Points: 1352 More actions · Answer 3

Nawar.Tabaa

Ten Centuries

Points: 1352

March 28, 2010 at 4:20 am

#1141054

Why do you need the row_num > 1?

Paul White SSC Guru Points: 150467 More actions · Answer 4

rds207 (3/27/2010)
can anybody please help ne writing the condition here ,
the procedure given in link is such a big one and i dont have permissions for doing so...

No-one's going to write it for you, no 🙂

I linked to the article, not to give you a copy-and-paste solution, but to give you ideas.

If you do not have the permissions or skill set to do this, pass the link to someone who does.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rds207 Old Hand Points: 344 More actions · Answer 5

row_num is the indentity column i have created in the temp table , where the run_duration > 18000 data enters into temp table and this colomn values increases by 1.

i created this column just to know that there are some records which got hanged and an email notification has to be sent based on this row_num ...that means this row gets incremented only when the jobs get hanged and hanged job data comes into temp table, and every time i run the job say for every day or so i truncate the temp table , so that each time the job runs it chechks the data in sysjobhistory table and if any data with run_duration > 18000 , sends it to temp table which should trigger alert email ...