7 Replies Latest reply on Oct 1, 2021 10:59 AM by Md Ahmed Aziz

    IDQ data load issue

    Md Ahmed Aziz Guru

      Hi Team,


      we have created a mapping and it is running successful without error, but the data is not getting loaded to Database and again if checker router it is coming as insert as the data did not got loaded. there is not error in the log file also, Please see the below screenshot it is showing as applied but record at DB level inserted.





        • 1. Re: IDQ data load issue
          Robert Whelan Guru

          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.

          1 of 1 people found this helpful
          • 2. Re: IDQ data load issue
            Md Ahmed Aziz Guru

            Hi Robert,


            Thanks for the suggestion, let me try with flat file and get back.

            • 3. Re: IDQ data load issue
              Krishna Gangwar Seasoned Veteran

              Hi Aziz,


              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')].





              • 4. Re: IDQ data load issue
                Md Ahmed Aziz Guru

                Hi Krishna,


                Sorry, I'm busy with different work which is on priority. may be next week I will resume this work,




                • 5. Re: IDQ data load issue
                  Md Ahmed Aziz Guru

                  Hi Team,


                  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



                  • 6. Re: IDQ data load issue
                    Nico Heinze Guru

                    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.




                    • 7. Re: IDQ data load issue
                      Md Ahmed Aziz Guru

                      Hi Nico,


                      Thank you for quick response, you are correct after making sure there was no rejection while processing all the correct data got loaded.