I have interesting issue observed when using teradata sql assistant(14.1) and Informatica tool (9.5) versions.
I created SQL code in teradata where source count is 5000 records . I am using source and target database as teradata and trying to load using informatica tool . Its straight forward mapping with source>>sqlcode>>expression>>target.However on successful workflow run success rows (5000) does not match with Success target rows (4000) . I dont see any error /bad file entry.But when i query target table in teradata i see 5000 records.
During teradata installation i used system dsn with ODBC connections as default for transaction mode details . We do have option to select as ANSI or normal option.So my point is why counts are not matching when loaded using teradata.Teradata's default transaction mode rolls back entire transaction to last commit point whenever a sql statement fails. This is unlike ANSI transcation mode, which only fails the current statements and does not really rollback the entire transaction.
Please help me to resolve the issue .Are there any set of conection changes to be done while working with informatica?
I verified in lots of blogs and could not find any solution.