Get SSIS package Execution Status

When SSIS Package is executing by SQL Agent Job…
.
.
.
.
DTSExecResult result;
package = app.LoadPackage(packagePath, null);
try
{
package.Execute();
result = package.ExecutionResult;
}
.
.
.
.
Yes. It works fine. But in this case we are going to execute our SSIS package using SQL Server Agent job. Therefore above code is useless.

What I did in this case is, use sysjobhistory table. Each and every time start the SQL Agent job , one entry is stored in sysjobhistory table.  Just need to get the status from that entry.

You can find all the job history in sysjobhistory table while the job details stored in sysjobs table.  You need to connect SQL server with sa account to view this.

Following figure shows how to reach those tables.

Now you can easily get the execution status (run_status)  from sysjobhistory table by stored procedure.  Run_Status containing numbers 0 to 4.  Following stored procedure get the status of particular job.

That’s it.
But make sure that SSIS package is completed its execution, before you get the status.