Sql-server – Twice job results in sql server, last one is left in progress

sql serversql-server-2008ssis

I have the job MP – Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 11:50:00 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:50:00 PM
Finished: 11:50:26 PM
Elapsed: 26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".
Here is the sql I use to run my report :

SELECT *  from  msdb..sysjobhistory WHERE run_status != 1

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.

Best Answer

This will give you the desired results :

select h.server as [Server],
    j.[name] as [Name],
    h.message as [Message],
    h.run_date as LastRunDate, 
    h.run_time as LastRunTime
from msdb.dbo.sysjobhistory h
    inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
        where j.enabled = 1 -- only check for enabled jobs
        and h.instance_id in
        (select max(h.instance_id)
            from msdb.dbo.sysjobhistory h group by (h.job_id))
        and h.run_status != 1 
/*0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled*/