Informatica Reject File - How to Identify rejection reason

Informatica Reject File - How to Identify rejection reason

author :mbharathc@gmail.com

When we run a session, the integration service may create a reject file for each target instance in the mapping to store the target reject record. With the help of the Session Log and Reject File we can identify the cause of data rejection in the session.Eliminating the cause of rejection will lead to rejection free loads in the subsequent session runs. If the Informatica Writer or the Target Database rejects data due to any valid reason the integration service logs the rejected records into the reject file. Every time we run the session the integration service appends the rejected records to the reject file.

Working with Informatica Bad Files or Reject Files

By default the Integration service creates the reject files or bad files in the $PMBadFileDirprocess variable directory. It writes the entire reject record row in the bad file although the problem may be in any one of the Columns. The reject files have a default naming convention like [target_instance_name].bad . If we open the reject file in an editor we will see comma separated values having some tags/ indicator and some data values. We will see two types of Indicators in the reject file. One is the Row Indicator and the other is the Column Indicator 

For reading the bad file the best method is to copy the contents of the bad file and saving the same as a CSV (Comma Separated Value) file. Opening the csv file will give an excel sheet type look and feel. The first most column in the reject file is the Row Indicator , that determines whether the row was destined for insert, update, delete or reject. It is basically a flag that determines the Update Strategy for the data row. When the Commit Type of the session is configured as User-defined the row indicator indicates whether the transaction was rolled back due to a non-fatal error, or if the committed transaction was in a failed target connection group.

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

Now comes the Column Data values followed by their Column Indicators, that determines the data quality of the corresponding Column.

List of Values of Column Indicators:

Column Indicator	Type of data	Writer Treats As
D	Valid data or Good Data.	Writer passes it to the target database. The target accepts it unless a database error occurs, such as finding a duplicate key while inserting.
O	Overflowed Numeric Data.	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 Value.	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.	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.

Also to be noted that the second column contains column indicator flag value 'D' which signifies that the Row Indicator is valid.

Now let us see how Data in a Bad File looks like:

0,D,7,D,John,D,5000.375,O,,N,BrickLand Road Singapore,T

 

Split Your InformaticaPowerCenter Target File Dynamically Based on the Content

  Flat File , Mapping Tips

There can be scenarios, where you need to generate multiple flat file using an informatica mapping based on the source data content or some other business rule; from a single data source. For example, you may need to generate last months top revenue generating customer list, which is split into multiple files based on the customer residence state. In this article we will see how to split your target file dynamically based on the business rule.

For the demonstration purpose, lets take a simple business case where we need to split the customer data into multiple files based on the customer residential state.

As the first step lets create the flat file definition using Target Designer

Now lets add one new column 'FileName' using the 'Add File Name Column' button which is highlighted at the top right corner as shown in the below image. This is the column based on the file name is dynamically changed.

Lets build the mapping as we have it in below image, Here we are sorting the data using the SORTER transformation. After the sorting, we will get the customers grouped together based on customer's state of residence.

 

Now we need to adding an EXPRESSION transformation to set a boundary flag for each customer group based on customer's state of residence. We are creating a port NEW_FILE_FLAG for the same

In the expression transformation we are adding following ports.

• V_NEW_FILE_FLAG as Variable Port.
• V_STATE  as Variable Port.
• NEW_FILE_FLAG  as Output  Port.

And the following are the expressions for the ports.

• V_NEW_FILE_FLAG :- IIF(STATE = V_STATE, 'N', 'Y') 
• V_STATE :- STATE 
• NEW_FILE_FLAG :- V_NEW_FILE_FLAG

Apart from setting NEW_FILE_FLAG, we need to create the port FileName, based on which the target file name is dynamically changed.

• FileName as Output  Port.

And the following is the expression for the port.

• FileName :- 'Customer_Master_' || STATE || '.csv'

 Based on the flag we created in the EXPRESSION transformation, we are going to split the file in the next step. We will use TRANSACTION CONTROL transformation to do the same. Set the 'Transaction Control Condition' property of the transformation with the below expression.

• IIF(NEW_FILE_FLAG='Y',TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

Now the mapping will be as we have it in the picture.

 

 

As the last step map all the columns to the target table. Our finished mapping will look as it is in the image below

 

 We are all done... Now build and run the workflow; you will see multiple files is generated in your Target File directory with Customer_Master_XX.csv as file name. There is not any specific setting required at the session level.