Informatica : Error threshold for each session

This article explains how you can set the Error threshold for a given session in Informatica. This is based on a few posts I made here on a discussion thread.

The default value for this parameter is “0” (Zero) which indicates unlimited non-fatal errors allowed. The session will “succeed” even if all the rows in the file/table error out because of non-fatal errors.

You can specify this option at the session level (work flow manager) as shown below.

As you can see, the drawback to this implementation is that

a) the value is hard coded and if you want to change the threshold, you need to checkout the session, change the value and promote it to all environments as needed.
b) If you want to have a standard threshold for all your loads and you want to change the threshold, it is quite a task to go to each of the sessions and change this value.

The solution to these two is to use the Informatica built-in variable $PMSessionErrorThreshold. You can set the value at the Integration Service level logged in as Administrator. Like any other parameter, you can override this using a parameter file entry.

Once you set this variable, you can use it to set the threshold for your session as shown below.

Once you set these options, you’ll see that the session fails after 100 non-fatal errors.

To simulate this, I created a dummy table with two columns and made one of them not null as below.

Then, I imported the same table as source and target and included the following query in the Source Qualifier Override.

All the 200 rows generated will have ID null which violates our not null constraint. so all 200 rows fail the load. Once the threshold 100 is crossed, the session fails (and the work flow fails if we set the “Fail parent if this task fails” option.)

Here is a snippet from the log file for this session.

As I mentioned earlier, once set using the integration service administration control, you can override this for each session at the parameter file level.

In the above example, the parameter file had the value of 100 which overrides the value set at the integration service level (which is zero).

Hope this helps…

Cheers!
Rajesh.

  • Pingback: Informatica Workflow Successful : No Data in target ! | ETL-Developer.com()

  • Basha

    Thanks a lot for giving clear explanation, reallly its very good exaples and i understand very much

  • Sasmita

    very good explantation…do i have to set using the integration service administration control to override this for each session at the parameter file level.

    and how to do it…

    • Rajesh

      No. You can set it in the parameter file and that would override any other settings set in the integration service admin. Changing the value in the admin control is not recommended as there might be other processes which might be impacted, whereas the parameter file is usually specific to one particular workflow.