Not sure about the tool but I have an idea which might helpful for you.
Load your table data into one flat file using one to one mapping and compared it with source flat file.
No. I don't want to do that .
Given a situation that my flat files are placed anywhere on windows/unix, any interface which can help me do a MINUS comparison on file and table data.
Did you take a look at the PowerCenter Data Validation Option? It is meant to perform comparisons of data, so might be the solution you're looking for.
Another option would be to build a mapping; sort the flat file by ID, sort the DBMS table contents by ID, then do a Full Outer Join between the two data streams; then add an EXP after the Joiner which checks whether (in case of identical IDs) all attributes are identical; if they are, filter out that record, and write everything else to some "error" target indicating which records do not match (or do not exist in either source).
Nonetheless it's tedious to set up and maintain such mappings for varying file and table formats, so - as mentioned by Remy - DVO probably is the better option here.
You can use a joiner transformation to join flat file and relation DB on all the columns, and pass the output to a relational DB.
Then you can just do a count of records on the target table. If that matches with the file count then you can conclude that your flat file and relational DB matches exactly
Pls use a joiner transformation to achieve the data comparison between flat file and relational DB.
Perhaps go for the PowerCenter Data Validation Option
Let us know your progress on this issue..
Mark the discussion as answered if there are no addtional questions/issues.
There is a PowerCenter tool called Data Validation option which helps you in validating the data. This tool helps in validating the Source and Target data to ensure that the tranformation logic did not alter the data erroniously.
Please find attached Informatica DVO user guide which will help you understand the concepts.