Errors not Bubbling Up in SP in Informatica

Question

In my Informatica mapping, when an SP is called via an unconnected stored procedure transformation, the workflow succeeds. However there is a divide-by-zero error in the SP and ideally the workflow should fail. The source and target used in this mapping are dummy flat-files.

However, when I use dummy tables instead for source and target, with a connected SP transformation this time, the error bubbles up successfully.

Any idea why this would happen? Why does the error show only with source table and connected SP transformation, and not with flat-file source unconnected SP transformation?

Answer 1

I've been dealing with it. One thing you can try is using SET NOCOUNT ON in Stored Procedure. Otherwise the x rows affected message can be treated by PowerCenter as the indicator that Stored Procedure has been successfully executed. No matter what else would be returned.

The other thing is it depends on whether you're using Native or ODBC Connector - with the latter the errors are not properly escalated. You can read more about my tials here: http://powercenternotes.blogspot.com/2014/09/ms-sql-server-stored-procedure-error.html