SSIS OnPostExecute event: How to see the execution result?

Question

I log SQL task activity to my own table using OnPreExecute and OnPostExecute which works fine. Here is the OnPreExecute code:

declare @audit_key int = ?
declare @package_name varchar(100) = ?
declare @task_name varchar(100) = ?

insert into fct.audit_avetmiss_detail
           (audit_key
           ,package_name
           ,task_name
           ,processing_start
, running_flag
)
select @audit_key, @package_name, @task_name, getdate(),1

..and OnPostExecute

declare @audit_key int = ?
declare @package_name varchar(100) = ?
declare @task_name varchar(100) = ?
declare @rows_source int = ?
declare @rows_inserted int = ?
declare @rows_discarded int = ?
declare @rows_updated int = ?

update fct.audit_avetmiss_detail
set Processing_end= getdate()
, running_flag = 0
, rows_source = @rows_source 
, rows_inserted = @rows_inserted 
, rows_discarded = @rows_discarded
, rows_updated = @rows_updated
where audit_key=@audit_key 
 and Package_name=@package_name 
 and Task_name=@task_name 

How to access the Execution result of 'Success' or 'Failure' in OnPostExecute?

Answer 1

In general, You have to use System variables to get the execution result and the error occurred (if exists), but based on the official documentation OnPostExecute event handler does not have access to System Variables that return the Execution Result or Error Description.

@[System::ExecutionStatus] : OnExecStatusChanged event handler

@[System::ErrorCode] : OnError event handler

Maybe you should log error when they occured OnError event handler, then you will assume that the package is failed. And don't use the OnPostExecute to log the Package Success. Instead of that add the Execute SQL Task at the end of package control flow. Then if all component success it will be executed and audit the package success.

For more information refer to the following documentation:

• System Variables for Event Handlers