1 of 1 people found this helpful
I'd suggest changing the target to a flatfile as a test. If the data gets written to the flatfile target, the issue is in the write to the database.
If nothing is written to the file, it's further back in the mapping?
You can also try adding a Expression Tx immediately before the database target object in the mapping and run a Data Preview to verify the data is getting that far.
Thanks for the suggestion, let me try with flat file and get back.
If the issue is still not resolved, I would suggest you to look into the mapping logs for "transformation errors". As per default behaviour, if there is a transformation error for a particular row, that row will be skipped. If you need any help with that, you share the mapping logs or open a case to assist you further.
One such example of transformation error:
2020-09-16 07:06:05.258 <TASK_10568-TRANSF_1_1_1> SEVERE: [TT_11131] Transformation [Router_r1] had an error evaluating variable column [PARTY_ID_out]. Error message is [<<Expression Error>> [ERROR]: transformation error
... u:ERROR(u:'transformation error')].
Sorry, I'm busy with different work which is on priority. may be next week I will resume this work,
Sorry for coming back so late, The issue still occur, as suggested when I tried flat file as target everything is getting loaded. the issue seems to be with certain type of data which I'm not able to recognise.
In log summary it shows as "applied" but there is no record inserted at db level
WRT_8036 Target: Write_mdm_location_merge (Instance Name: [Write_mdm_location_merge])
WRT_8038 Inserted rows - Requested: 20 Applied: 16 Rejected: 4 Affected: 16
Do you try to load into SQL Server using an ODBC connection?
In this case I would say, that's normal. Unfortunately. My experience is that the SQL Server ODBC driver provided by Data Direct (these drivers are part of the Informatica Platform installation, Informatica provides these drivers based on an OEM agreement with Data Direct) "swallows" target records if at least one record during that same load has been rejected by SQL Server.
An example (which is real life, not hypothetical): in one of my projects I've sent 110 records to some SQL Server table. Out of these 110 records app. 40 bear an invalid string as a date value, so SQL Server rejects those 40 records.
PowerCenter reports that the other 70 records have been loaded.
However, the database does not contain these records, they simply vanished.
After some research I have found one way to avoid these records to vanish. I must not load them to the target at all. Meaning I have to make sure beforehand (e.g. within the mapping itself) that all records going to the SQL Server target are valid and acceptable by SQL Server, meaning no record gets rejected. Then only will the valid records appear in SQL Server.
No idea why the ODBC driver to SQL Server behaves this way, it's just a matter of fact that it does.
Unfortunately you haven't written what's your target DBMS, so that's only a guess.
Thank you for quick response, you are correct after making sure there was no rejection while processing all the correct data got loaded.