28 Jan 08 16:43
I am using Informatica 7.1.5 with Oracle 8g database.
Ok here is my situation
Our current ETL tool is DataStage and we bought Informatica and we are in the middle of converting all of our existing ETL jobs.
So what I do is, redesign an existing ETL job in Informatica to do EXACTLY the same thing as the original DataStage ETL job. Thus, as part of my unit testing, I have to make sure that not only the row count in the target table is the same but that every row has exactly the same data in each column, as with the original table.
In this regard I have to compare two tables that in theory should be identical.
DataStage ETL job populates target Table A
Informatica mapping/workflow populates target Table B
Table A and Table B SHOULD be identical, if I have done the redesign in Informatica correctly.
If I do this testing in SQL, within the database and check for each column, it is very time consuming.
I was hoping there would be a cool way to do this in PowerCenter Designer. Perhaps something like
- create a new mapping
- make Table A your Source table
- make Table B a lookup transformation
- drag all the ports from Table A to the lookup transformation, and then check for equality
- then somehow use a router transformation that gives me all the PASS and FAIL. The failures would be where for a single row, the data in TableA.colX did not match TableB.colX. However, I want the mapping to tell me for which row, which columns (ports) did not match.
Is this possible?