The SQL Agent job is returning DTSER_COMPLETION (2) after running an SSIS package. The task of SSIS is completing correctly but is showing many of the following errors:
......Removing this unused output column can increase Data Flow task performance
I am not sure whether this can be solved by modifying something in SSIS or the SQL Agent job. I do not want to modify the SSIS package since it is being supplied by an outside source.
The is being run on SQL Server 2008
Error handling in SSIS is a bit awkward, I've had problems with packages following OnError constraints correctly and completing fine, but the SQL Agent still reports failure. My solution was: define OnError event handlers for the steps that are 'allowed' to fail, and then set the Propagate system variable to false.
If you have too many individual steps to make that practical, you can group them in sequence containers and create the event handlers for each sequence instead. It seems to work fine for me, but it took me some time to work out the solution and I couldn't find any good documentation on error handling in SSIS so I don't know if this is a good solution or just a hack.