Developer forum

Hello Skylar,

- could you provide a print screen from your Destination settings?  I like to see, what other options you have selected beside "Use strict primary key matching".

And have you selected key columns in the mappings section?

Br. Michael Knudsen

HI Skylar

i dont see duplicates on your website, base on configuration its working without any issue.

if you can highlight specifically which data has duplicated, then can help more

Hi Michael, I'm having trouble replying with screenshots. Hopefully they are attached so you can see the destination settings and integration job PKs.

Hi Shiwanka, the job throws a dupliate PK error when I try to import the xml I've provided. Instead of updating the product (which already exists in the db), it seems like it's trying to create a new row and erroring.

Destination settings: https://app.screencast.com/0GqRa0jg34WYx

PKs on job: https://app.screencast.com/w41mtA543jFUE

your settigs is correct and data is added correctly, where was the duplicates?

i can not find any duplicates 

Data is added correctly initially, but when I try to reimport an existing product using the XML provided, I get a duplicate PK error thrown on the job.

The data does not import again to create a duplicate - but it doesn't update the existing row which is the problem.

Try adding VariantID to your mapping and assign it a constant value of an empty string. Then make VariantID part of your composite key. Does it work then?

And does it work with strict primary mapping key off?

Imar

Hi Imar,

I've tried both of those ideas. Even if the job is expecting VariantId (which should not be the case), why isn't strict primary key working as expected and only looking at ProductNumber/ProductLanguageId?

Thank you,

Skylar

It was just a hunch. In the database the composite key is ProductId, ProductVariantId and ProductLanguageId. I was thinking that a check without VariantId might cause it to look for VariantId = null. That would then return no existing records (since it's an empty string, not null in the database) but then the insert would fail because it would send an empty value for it causing a duplicate key issue.

Can you show more of your job and errors you are getting?

Imar

Hi Imar,

And I appreciate the ideas - this one is tricky, but some more context might help.

The customer will be setting up variants manually in DynamicWeb, so they will eventually look like this:

https://app.screencast.com/slaMlNTwqtUTw

With that said, even if we were to add an empty string value for ProductVariantId, the goal would still be to update the varianted product rather than creating a new record with a blank productVariantId. This is the reason for using ProductNumber as PK.

Thanks,

Skylar

Yes,. that all makes sense, as it should work the way you describe. I was just trying out steps in order to find the underlying issue.

>> Can you show more of your job and errors you are getting?

Can you provide more concrete details?

Imar

Hi -

I was successfully able to import a delta for 64507-017 when it already existed in the database - however, it created a new row AND updated the existing.

See before and after. This time was different because I did not get an error while running the integration job. Strict PK matching and Create Missing Groups are checked, and my PKs are ProductNumber and ProductLanguageId.

https://app.screencast.com/iHmmtqazFl6mq

Imported XML:

<?xml version="1.0" encoding="utf-16"?>
<tables batchId="{FCC0432A-8B46-4851-8F48-DBEBAF0BDE51}">
  <table tableName="EcomProducts">
    <item table="EcomProducts">
      <column columnName="ProductId"><![CDATA[64507-017]]></column>
      <column columnName="ProductNumber"><![CDATA[64507-017]]></column>
      <column columnName="ProductName"><![CDATA[LEXFEEL 7]]></column>
      <column columnName="ProductWeight"><![CDATA[50.00]]></column>
      <column columnName="ProductVolume"><![CDATA[0.00]]></column>
      <column columnName="ProductDefaultUnitId"><![CDATA[LB]]></column>
      <column columnName="ProductManufacturerId"><![CDATA[]]></column>
      <column columnName="ProductPrice"><![CDATA[0.00]]></column>
      <column columnName="ProductStock"><![CDATA[0.00]]></column>
      <column columnName="ProductHeight"><![CDATA[0.00]]></column>
      <column columnName="ProductWidth"><![CDATA[0.00]]></column>
      <column columnName="ProductLength"><![CDATA[0.00]]></column>
      <column columnName="ProductInciName"><![CDATA[]]></column>
      <column columnName="ProductActive"><![CDATA[False]]></column>
      <column columnName="Groups"><![CDATA[]]></column>
    </item>
  </table>
</tables>

Is it because you already map Product ID? it seems to be changing the product ID from an auto-generated ID to the incoming ID. Try removing it from the mapping or set it to "Insert only".

Hi Imar, good suggestion, I set it to insert only and imported and it again created the extra row.

Hello Imar,

We did a little bit more troubleshooting to narrow down what the issue might be. 

Up until now, each data import resulted in the duplicate PK error, despite only one record with that PK existing in both source and destination tables. Then, we checked "Update only existing records" in the destination settings, the job ran successfully. This implies that the data structure / keys are correct, and could indicate a bug in the EcomProvider. Leaving it unchecked should allow inserts and updates to happen, which is what we need but is not happening. 

I'm hopeful that this information gives you a lead on what to investigate further. Let me know if more information can help.

Best regards,

Skylar Kennedy

There's not much I can investigate further; I'm just a forum user like you. If you think it's a bug, I'd report it to Care. For your own testing, and for the report to Care, it would be helpful to create the simplest job with the simples XML source file to repro the problem.

Cheers,

Imar

Thank you.

Hi Skylar,
it looks like you need to use the Dynamicweb Provider as your Destination provider instead. The problem is that the EcomProvider does some things behind the scene like searching for the existing products and uses the first found product Id. It also adds the mapping to the empty ProductVariantId in order the sql query for insert to be working, so it will not update the products where variantId is not empty (when insert/update is ON by default), here are similar posts about the problem and how to solve it:
https://doc.dynamicweb.com/forum/integration/integration/update-stock-in-multi-language-site
https://doc.dynamicweb.com/forum/integration/integration/up-date-multiple-products-from-single-source

BR, Dmitrij

Hi Dmitrij,

Does that mean one should never use the EcomProvider when you have variants and want to update by product number? I guess it would work if you'd use a table script to map a product number to a product ID + variant ID + language ID combination.

Imar

Hey Imar and Dmitrij, 

Thanks for all the comments and looking into this issue so far!  I am with Imar a bit here, and even your own comments about the behind the scenes logic the EcomProvider does and we are wanting to use those smarts versus build out that logic in table scripts, staging with sproc, etc.  In DW9 in a previous provider there was some "smarts" around the ProductId and the primary key where the provider was electing to help find a match but in the end it wasn't helpful because a primary key needs to be a perfect match exact.  That was when the setting for "Use strict primary key matching" was introduced.  That matching was exact on the particular field selected in the data integration job for primary key without smarts to attempt to find first or a near matching ProductId to the one in the data integraiton source file. 

That functionality to honor the exact match on primary key and specifically only the primary keys which are selected is what we are expecting and looking for because otherwise the data cannot be imported or updated because the same record is not found each import attempt. Of course, we can use the DynamicwebProvider but taking advantage of the smarts in the EcomProvider has value when working properly.  

I read the other post which give workarounds for the EcomProvider not honoring the settings and data being unquie. It feels like a bug that was introduced with impact because using the DynamicwebProvider introduces further development needed that the EcomProvider does already.  Thoughts? 

Thanks, Shawn 

Hi Shawn,
you can split the job into two jobs: one will use the "Insert only new records" and the second "Update only existing records".
In that way the update job will update the products found by ProductNumber (the ProductVariantId, ProductLanguageId will not be auto added for the update only job if they are not present in the mappings).
The insert job will auto add the ProductId, ProductLanguageId, ProductVariantId columns if they are not present in the mapping.
BR, Dmitrij

Hi Dmitriy,

The Update Only Existing Records job runs successfully, as every record already exists in the destination table. Insert Only New Records job fails, even though it should be successfully inserting 0 new records since there are no new records in the source.

PKs for insert only job: https://app.screencast.com/d6Dul9rfJtehO

Error: Job Failed with the following message: failed to move data from temporary table [dbo.EcomProductsTempTableForBulkImport1] to main table [dbo.EcomProducts]. Exception: Violation of PRIMARY KEY constraint 'DW_PK_EcomProducts'. Cannot insert duplicate key in object 'dbo.EcomProducts'. The duplicate key value is (69025-018, LANG1, ).

Any thoughts, or am I missing another setting?

Thanks,

Skylar Kennedy

Hi Skylar,
try to remove the ProductAutoId from the mappings:

BR, Dmitrij

Hi Dmitrij,

Thanks for the suggestion, but it didn't work, do you have any more ideas?

Regards,

Skylar Kennedy

Hi Skylar,
try to sort the column mappings so the ProductNumber key column is the first or is placed before the ProductLanguageId key column.
BR, Dmitrij

Hi Dmitrij,

Interestingly, that seemed to at least help a little bit, so that the insert-only job ran to completion which is a step in the right direction. However, the outcome was not ideal, please see my before and after DB screenshot with comments on my findings.

https://app.screencast.com/v43MjPXOjbzp6

Thank you!

Skylar Kennedy

Hi Skylar,
When the ProductId is in the mapping the search of the existing product is not made, so try to remove the ProductId from the mapping. Once the new product will be inserted it will get a newly generated id like "ImportedPROD%".
BR, Dmitrij

Hi Dmitrij,

We scrapped the job with staging tables and decided to start fresh with a new integration job that strips the mappings to the bare-bones fields, used the ecomProvider to load them into EcomProducts, and it miraculously worked. However, doing so uncovered two issues:

1. Skylar and Shawn TEST PRODUCT IMPORT integration job had a mapped constant of LANG1 for ProductLanguageId, but when the job ran it threw this error: Job Failed with the following message: failed to move data from temporary table [dbo.EcomProductsTempTableForBulkImport1] to main table [dbo.EcomProducts]. Exception: Cannot insert the value NULL into column 'ProductLanguageId', table 'staging-inolex.mydwsite.com.dbo.EcomProducts'; column does not allow nulls. INSERT fails. This was unexpected since we were not inserting the value NULL, we were inserting a constant of LANG1 which exists in EcomLanguages. When we inserted that field and value into the source xml and mapped it from ProductLanguageId>ProductLanguageId, the job ran to completion. We need the job to be able to set constants.
2. I noticed that the Use Strict Primary Key matching definition changed - was something in the EcomProvider updated during the time we've been troubleshooting this issue? This possibly relates to my other thread for the same solution. We are now able to run the job as expected with primary keys being honored. See screenshot of the before definition and current, you will notice that the current definiton needs to be fixed:
   Current: https://app.screencast.com/kJY3bpaFI2MXf
   Definition as of 5/14: https://app.screencast.com/WPv9YwPg3cWbM

Thank you,

Skylar Kennedy

Hi Skylar,
1) this was a bug #24450 in a recent DW10 version, it was fixed and will be released soon (next week)
2) no, nothing was changed in the EcomProvider during last 2 weeks

BR, Dmitrij

Hi Dmitrij,

Okay for #1... but what about number two? Will we get the right setting description back for Use Strict Primary Key?

Thanks,

Skylar