Error Catching – Stored Procedure inside of SSIS
One fun thing I found out about SSIS is that is doesn't always error out a package if you execute stored procedures as SQL tasks instead of utilizing the SQL itself inside of the package. In other words - it may produce an error if you were to execute it in SSMS, but if you execute within SSIS it'll fly by successfully without you knowing anything went wrong. The best little trick I've found is to just surround the entire procedure with a Try/Catch block. This seems to raise the error up to SSIS and fails the package for you.
CREATE PROCEDURE usp_TestProc as
BEGIN TRY
--SQL goes here...
END TRY
BEGIN CATCH
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ' at line ' + CONVERT(varchar(50), ERROR_LINE()) + ': ' + ERROR_MESSAGE();
END CATCH;
GO
...