FORWARDING REJECTED ROWS property in an Update Strategy transformation & bad file structure

  

When we use the update strategy transformation we have the option to reject the row based on a criteria. We usually use DECODE or IIF to specify the criteria for rejecting the row[Treat Source Row should be selected as Data Driven at the session level if update startegy is used ]

IIF( ( RESIDENT_SK=0), DD_REJECT, DD_UPDATE )

The following table lists the constants for each database operation and their numeric equivalent:

Insert	DD_INSERT (Numeric Value 0)
Update	DD_UPDATE (Numeric Value 1)
Delete	DD_DELETE (Numeric Value 2)
Reject	DD_REJECT (Numeric Value 3)

Now coming to the Forwarding Rejected Row Option in update strategy, this option is used to forward the rejected rows into the bad file which we define at session level. If you disable this option, the rejected records won't be available in the bad file (reject file).

However the rejected rows will be available in session log (Normal logging level) even though the forwarding rejected row option is enabled\disabled.

These records in the reject file help us to understand the reason for rejection and validate the records not satisfying the business requirement. This helps to change the input data the next time we load the target

Bad file - 

Bad Files are often termed as Reject Files that holds the data for the entire row, which is rejected by the Target while writing. Whenever a Session is created with a Target, Reject Files (Bad Files) are also created along with it.

Why are bad files created  - 

Bad Files always extends the scope of error tracking. It helps you to get the record and the values which got rejected. And with the help of the information available in the Session Log, you can perform some analysis to track the exact error and take steps to correct it accordingly. You might also use the Bad File to send it to the users as an attachment with some modification to the record using Scripting. 

Where are the bad files created - 

By default, the Integration Service creates reject files in the $PMBadFileDir process variable directory. You need to explicitly define/override the value if you want to store the bad files in some other directory.

Structure of Informatica Bad Files or Reject Files

There are two types of Indicators in the reject file. One is the  Row Indicator and the other is the Column Indicator.

1. Row Indicators - 

• The first column in each row of the reject file is the row indicator.
• The row indicator defines whether the row was marked for insert, update, delete, or reject.

List of Values of Row Indicators:

Row Indicator	Indicator Significance	Rejected By
0	Insert	Writer or target
1	Update	Writer or target
2	Delete	Writer or target
3	Reject	Writer
4	Rolled-back insert	Writer
5	Rolled-back update	Writer
6	Rolled-back delete	Writer
7	Committed insert	Writer
8	Committed update	Writer
9	Committed delete	Writer

2. Column indicator

• Column indicators appear after every column of data.
• The column indicator defines whether the column contains valid, overflow, null, or truncated data. 

Column Indicator	Type of data	Writer Treats As
D	Valid data.	Good data. Writer passes it to the target database. The target accepts it unless a database error occurs, such as finding a duplicate key.
O	Overflow. Numeric data exceeded the specified precision or scale for the column.	Bad data, if you configured the mapping target to reject overflow or truncated data.
N	Null. The column contains a null value.	Good data. Writer passes it to the target, which rejects it if the target database does not accept null values.
T	Truncated. String data exceeded a specified precision for the column, so the Integration Service truncated it.	Bad data, if you configured the mapping target to reject overflow or truncated data.