How To Find the rejected Records in Informatica

Under Session Config Object, you must set the Error Handling Properties to log rejected records to Relational Database.

Once above setting is done, if not already then 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.

The Use the below query to run against the database to pull the rejected records.
You need to replace session name <SessionName> argument with the session name you are interested in.

With latestRun AS
  (SELECT TOP (1) [REPOSITORY_GID] ,
          [WORKFLOW_RUN_ID] ,
          [WORKLET_RUN_ID] ,
          [SESS_INST_ID] ,
          [SESS_START_TIME] ,
          [SESS_UTC_TIME] ,
          [REPOSITORY_NAME] ,
          [FOLDER_NAME] ,
          [WORKFLOW_NAME] ,
          [TASK_INST_PATH] ,
          [MAPPING_NAME] ,
          [LINE_NO]
   FROM [RDS].[dbo].[PMERR_SESS]
   WHERE 1=1
     AND TASK_INST_PATH LIKE '<SessionName>'
   ORDER BY SESS_START_TIME DESC)
SELECT *
FROM latestRun lr,
     PMERR_DATA pd
WHERE 1=1
  AND pd.SESS_INST_ID = lr.SESS_INST_ID
  AND pd.WORKFLOW_RUN_ID= lr.WORKFLOW_RUN_ID

SQL including Error Message.

WITH latestRun AS
  (SELECT [REPOSITORY_GID],
          [WORKFLOW_RUN_ID],
          [WORKLET_RUN_ID],
          [SESS_INST_ID],
          [SESS_START_TIME],
          [SESS_UTC_TIME],
          [REPOSITORY_NAME],
          [FOLDER_NAME],
          [WORKFLOW_NAME],
          [TASK_INST_PATH],
          [MAPPING_NAME],
          [LINE_NO]
   FROM [RDS].[dbo].[PMERR_SESS]
   WHERE 1=1
     AND TASK_INST_PATH LIKE 's_m_DV_EGM_Satellites_Load'
     AND SESS_START_TIME >= '2018-04-18' --ORDER BY SESS_START_TIME DESC
 )
SELECT *,
       pm.ERROR_MSG
FROM latestRun lr,
     PMERR_DATA pd ,
     [dbo].[PMERR_MSG] pm
WHERE 1=1
  AND pd.SESS_INST_ID = lr.SESS_INST_ID
  AND pd.WORKFLOW_RUN_ID= lr.WORKFLOW_RUN_ID
  AND pm.WORKFLOW_RUN_ID=lr.WORKFLOW_RUN_ID
  AND pm.SESS_INST_ID=lr.SESS_INST_ID
  AND pm.TRANS_ROW_ID=pd.TRANS_ROW_ID
  --AND SESS_START_TIME ='2018-04-18 13:22:29.000'
ORDER BY SESS_START_TIME

Rejected records are captured in table PMERR_DATA

Now How to interpret data in the "PMERR_DATA" table in PowerCenter?



Example


Data in the TRANS_ROWDATA column of the PMERR_DATA table is as follows:
38512.00000000000|N:|U:|U:|U:|U:|Y|U:|U:|U:|U:|U:|01/28/2013 12:53:38.000000000|U:


The column indicators are as follows:
D - Valid
O - Overflow
N - NULL
T - Truncated
B - Binary
U - Data Unavailable

The fixed delimiter between column data and column indicator is a colon ( : ). The delimiter between the columns is a pipe( | ). You can override the column delimiter in the error handling settings.

The Integration Service converts all column data to a text string in the error table or the error file. For Binary data, the Integration Service uses only the column indicator.  This value can span multiple rows. When the data exceeds 2000 bytes, the Integration Service creates a new row. The line number for each row error entry is stored in the LINE_NO column.