Informatica Workflow Successful : No Data in target !

This is a frequently asked in the Informatica forums and the solution is usually pretty simple. However, that will have to wait till the end because there is one important thing that you should know before you go ahead and fix the problem.

Your workflow should have failed in the first place. If this was in Production, Support Teams should know that something Failed. Report users should know the data in the Marts is not ready for reporting. Dependent workflows should wait until this is resolved. This coding practice basically violates the Age-old Principle of fail-fast when something goes wrong, instead of continuing flawed execution pretending “All is well”, causing the toughest-to-debug defects.

Of Course, this is not specific to Informatica. It is not uncommon to see code in other languages which follows this pattern. The only issue that is specific to Informatica is that this is the default behavior when you create a session. So you might have this “bug” in your code without even knowing it.

Stop On Errors:

Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. Non-fatal errors include reader, writer, and DTM errors. Enter the number of non-fatal errors you want to allow before stopping the session. The Integration Service maintains an independent error count for each source, target, and transformation. If you specify 0, non-fatal errors do not cause the session to stop.
Optionally use the $PMSessionErrorThreshold service variable to stop on the configured number of errors for the Integration Service.

In Oracle, it is the infamous “when others then null” .

In Java..Something like..

The solution to this problem in Informatica is to set a limit on the number of allowed errors for a given session using one of the following methods.
a) Having “1” in your default session config : Fail the session on the first non-fatal error.
b) Over-write the session Configuration details and enter the “Stop On Errors” to “1” or a fixed number.
c) Use the $PMSessionErrorThreshold variable and set it at the integration service level. You can always override the variable in the parameter file. Take a look at this Article on how you can do that.

Remember, if your sessions do not belong to one of these categories, you are doing it wrong!.
a) Your session Fails and Causes the workflow to fail whenever any errors occur.
b) You allow the session to continue despite some (expected) errors, but you always send the .bad file and the log file to the support/business team in charge.

Why is there no data in Target

The solution to “why the records didn’t make it to the target” is usually pretty evident in the session log file. The usual case (based on most of the times this question is asked) is becuase all of your records are failing with some non-fatal error.

The only point of this article is to remind you that your code has to notify the right people when the workflow did not run as planned.

Cheers!
Rajesh