Error tables in Informatica

To capture, the errors at the transformational level, kindly enable the property in the config object tab in the session property i.e "Error log type".

choose the option "Relation Database", along with this mention error log DB connection object in the next property.

Once its done, 4 tables are created in the database namely :-

- PMERR_DATA. Stores data and metadata about a transformation row error and its corresponding source row.

- PMERR_MSG. Stores metadata about an error and the error message.

- PMERR_SESS. Stores metadata about the session.

- PMERR_TRANS. Stores metadata about the source and transformation ports, such as name and datatype, when a transformation error occurs.

So now, you can verify the error, due to which the mapping the failing.

The error data stored in the error table, we can pull the error report using an SQL querry. Below is a basic query to get the error report. We can be more fancy with the SQL and get more information from the error tables.

select

sess.FOLDER_NAME as 'Folder Name',

sess.WORKFLOW_NAME as 'WorkFlow Name',

sess.TASK_INST_PATH as 'Session Name',

data.SOURCE_ROW_DATA as 'Source Data',

msg.ERROR_MSG as 'Error MSG'
from

ETL_PMERR_SESS sess
left outer join ETL_PMERR_DATA data
on data.WORKFLOW_RUN_ID = sess.WORKFLOW_RUN_ID and

data.SESS_INST_ID = sess.SESS_INST_ID
left outer join ETL_PMERR_MSG msg
on msg.WORKFLOW_RUN_ID = sess.WORKFLOW_RUN_ID and

msg.SESS_INST_ID = sess.SESS_INST_ID
where

sess.FOLDER_NAME = <Project Folder Name> and
sess.WORKFLOW_NAME = <Workflow Name> and
sess.TASK_INST_PATH = <Session Name> and

sess.SESS_START_TIME = <Session Run Time>