Managed to get to the bottom of this. So the SQL Server Agent job is failing with a DTS_Exec_Result of ‘2’ for ‘completion’ but appears to be completing the steps OK. The SQL Server Agent expects a DTS_Exec_Result of ‘0’ for ‘success’ or ‘1’ for ‘failure’, see following link:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsexecresult.aspx

The DTS_Exec_Result can be forced in the SSIS package while in Visual Studio

In this case, the ‘ForceExecutionResult’ was set to ‘completion’ which was causing the SQL Agent Job to fail. Assuming the package normally runs fine this should be set to ‘none’ as the ‘DTS_Exec_Result’ is determined by the output of the package.