3 Replies Latest reply on May 16, 2019 12:29 PM by Vincent McKinney

    Data Quality Mapping is trying to update my key column

    Kevin Koshinski New Member

      I am reading address data from a table called "People" in SQL Server, using Informatica Developer, parsing the full address using Address Doctor, and then updating a couple discrete address fields like City, State, etc.  I am having a lot of problems updating the target, however.  Basically, I am reading and writing to the same table, so there is no reason to insert or delete.  When the mapping runs, it's trying to update all of the mapping columns, including the primary key.  I want it to match on the key, but not try to update it.  Any advice?

        • 1. Re: Data Quality Mapping is trying to update my key column
          Vincent McKinney Active Member

          Hi Kevin - Are you using an Update Strategy transformation prior to writing to the target table? And do you have the Primary Key for your People table identified as such in the Informatica Data Object? If those are set up correctly then it should not be trying to insert new records or update the value in the primary key column... otherwise we might need more details on the mapping & transformations to dig deeper into this.

          • 2. Re: Data Quality Mapping is trying to update my key column
            Kevin Koshinski New Member

            Hi Vincent,

             

            Thanks for your reply.  I don't have much experience with Informatica Developer, since we are primarily IICS Data Integration customers.  Right now the mapping essentially does this:

             

            Source (dbo.People)  --> Address Validation --> Target (dbo.People)

             

            Both the source and the target are the same table.  The primary key column is a passthrough on the Address Validation.  I'm not familiar with the Update Strategy, but that sounds like the missing piece here.

             

            We really just need to define the "update" column (the column compared between and source and target to find existing rows), and which columns actually need to be written to.  I'm overstating just to make sure I am following you correctly.  Update Strategy doesn't exist in Data Integration Cloud, so this kind of makes sense.

            • 3. Re: Data Quality Mapping is trying to update my key column
              Vincent McKinney Active Member

              Yep, I think you are on the right track then! Like you said, it sounds like you will need to add an update strategy transformation between Address Validation & the target. The Developer Transformation Guide has some useful info on this.

               

              You'll also need to make sure that your Primary Key in the target table is identified. This can be done in the actual data object for dbo.People (to view the data object, click on the link in the "Data Object" properties tab of the Target in your mapping.) In the Data Object itself, on the Overview tab, make sure the "Primary" box is checked for your PK.

               

              Once that's done, connecting the port for that PK from the Update Strategy to the Target should identify it as "update" column to match on, while any other fields that are connected will then be updated for that particular record.

               

              For the Update Strategy Transformation, you'll want to set the Update Strategy Expression to DD_UPDATE (or just the number 1) if you only want to update records in the target table as the records go through your mapping. If you want to do a mix of inserts and updates, you'll need some additional logic - something like a Lookup transformation using a Dynamic Cache could help you distinguish between inserts & updates... chapter 27 of the Developer Transformation Guide can walk you through that.

               

              Hope this helps! Good luck!