I have used this technique with all versions of sql server up to 2014, and none of them return the detailed error description raised by the database engine that you get when the package fails. This is the biggest limitation of SSIS, and a common requirement of many ETL processes that can be done with many other ETL tools.