Informatica writes rejected rows into a bad file, how to avoid that?

data-warehouse etl informatica informatica-powercenter
Matkrupp picture Matkrupp · May 3, 2013 · Viewed 9.1k times · Source

I have developed an Informatica PowerDesigner 9.1 ETL Job which uses lookup and an update transform to detect if the target table has the the incoming rows from the source or not. I have set for the Update transform a condition

IIF(ISNULL(target_table_surrogate_id), DD_INSERT, DD_REJECT)

Now, when the incoming row is already in the target table, the row is rejected. Informatica writes these rejected rows into a .bad file. How to prevent this? Is there a way to determine that the rejected rows are not written into a .bad file? Or should I use e.g. a router insted of an update transform to determine if the row is insert row an then discard the other rows?

Answer

momobo picture momobo · May 3, 2013

Put a filter transformation before the update strategy transformation and filter away the bad rows

Related questions

What is a staging table?

Are staging tables used only in Data warehouse project or in any SSIS Project? I would like to know what is a staging table? Can anyone give me some examples on how to use it and in what circumstances it …

Read More
How to extract data from Google Analytics and build a data warehouse (webhouse) from it?

I have click stream data such as referring URL, top landing pages, top exit pages and metrics such as page views, number of visits, bounces all in Google Analytics. There is no database yet where all this information might be …

Read More
Which is better, ETL or ELT?

Having spent some time working on data warehousing, I have created both ETL (extract transform load) and ELT (extract load transform) processes. It seems that ELT is a newer approach to populating data warehouses that can more easily take advantage …

Read More