Error Logging in PowerCenter

In order to capture any Informatica PowerCenter errors into a flat file or database during runtime, Informatica Corporation suggests row-level error logging. The major disadvantage with this is that the performance of the workflow is affected because of row-level processing as opposed to block processing.

In order to overcome this, a simple approach can be followed that can be a common approach for all the workflows. This approach is based on the fact that $Session_Name.ErrorMsg will store NULL value if the session runs fine, otherwise stores the latest error message from the Session run.

1) Create two workflow variables - one for Error message $$ERROR_MESSAGE and the other $$SESSION_NAME to store the failed session name.

2) Create an assignment task in the workflow and create links to it from each of the sessions. Please note that the flow should be TOWARDS the assignment task from the sessions.

3) Modify the link expression for all these links to $Session_Name.PrevTaskStatus = FAILED.

4) In the assignment task, assign $Session_Name.ErrorMsg to the workflow variable $$ERROR_MESSAGE and assign Session_Name to $$SESSION_NAME.

5) You need a bit of nested iifs to achieve this.

For variable $$ERROR_MESSAGE, the expression contains

:udf.if_null_or_blank($Session_Name_1.ErrorMsg,
    :udf.if_null_or_blank($Session_Name_2.ErrorMsg,
        :udf.if_null_or_blank($Session_Name_3.ErrorMsg,
            :udf.if_null_or_blank($Session_Name_4.ErrorMsg ,
                :udf.if_null_or_blank($Session_Name_5.ErrorMsg ,
                    :udf.if_null_or_blank($Session_Name_6.ErrorMsg ,
                        :udf.if_null_or_blank($Session_Name_7.ErrorMsg ,
                            :udf.if_null_or_blank($Session_Name_8.ErrorMsg ,
                                :udf.if_null_or_blank($Session_Name_9.ErrorMsg ,
                                    :udf.if_null_or_blank($Session_Name_10.ErrorMsg ,
                                        :udf.if_null_or_blank($Session_Name_11.ErrorMsg ,
                                            :udf.if_null_or_blank($Session_Name_12.ErrorMsg ,
                                                 'A Fatal Error occurred' 
                                 ,$Session_Name_12.ErrorMsg
                                 )
                              ,$Session_Name_11.ErrorMsg
                              )
                           ,$Session_Name_10.ErrorMsg
                           )
                        ,$Session_Name_9.ErrorMsg
                        )
                     ,$Session_Name_8.ErrorMsg
                     )
                  ,$Session_Name_7.ErrorMsg
                  )
               ,$Session_Name_6.ErrorMsg
               )
            ,$Session_Name_5.ErrorMsg
            )
         ,$Session_Name_4.ErrorMsg
         )
      ,$Session_Name_3.ErrorMsg
      )
   ,$Session_Name_2.ErrorMsg
   )
,$Session_Name_1.ErrorMsg
)

:udf.if_null_or_blank(input_String_Argument, output_if_NULL_Blank, output_if_not_NULL_Blank) is a user-defined function with expression contents

iif(isnull(input_String_Argument) or length(ltrim(rtrim(input_String_Argument)))
= 0, output_if_NULL_Blank, output_if_not_NULL_Blank)

In the same way, create the expression for the $$SESSION_NAME. It should be the same expression as for the $$ERROR_MESSAGE but in the else part of the iif(), the session names should be specified instead of ErrorMsg.

6) From this assignment task take a link to a session which stores the contents of these workflow variables into a database table or a flat file. let us call this session mapping as LOG mapping.

You may question the scope of these workflow variable inside the Log mapping. If you can use the workflow variable in the source qualifier SQL override, then you can get the data from the same. Like this:

select '$$ERROR_MESSAGE', '$$SESSION_NAME'
from dual

Take 2 ports out of the source qualifier onto a expression transformation and then continue loading into a relation table target or a flat file target.

7) It is IMPORTANT to make sure that the General tab property of the Assignment task --> Treat Input Links as "OR". This makes sure if at least one session fails, the assignment task is triggered and the error is logged.

If you implement the Error Logging this way, you will be able to catch all kinds of Informatica errors.