I have an SSIS package with a number of data flow tasks with OLE DB Source tasks that execute SP's.
I'd like SP to have the ability to return somewhat English-sounding error message if an error occurs within the SP.
Doing this within a SP using a TRY/CATCH block is no biggie, but I'm trying to figure out how to handle this within my SSIS data flow task.
As far as I can tell, with OLE DB Source tasks if there is any difference between what is returned, and what is pre-mapped to an OLE DB Destination task, SSIS will throw a runtime error, with different error properties that what would be trapped by a TRY..CATCH block within T-SQL.
Has anyone pulled this off?
Thanks in advance.