Developer forum

I ran into this again, but now with a slightly different source data, Also, it no longer works with the Dynamicweb Provider. Here's my source data

<table tableName="EcomProducts">
  <item table="EcomProducts">
    <column columnName="ProductNumber"><![CDATA[Z17040045]]></column>
    <column columnName="ProductStock"><![CDATA[22,00]]></column>
  </item>
</table>

And here's the existing data, across 3 languages:

Here's the job setup:

I have marked ProductNumber as the key.

When I run the job, I get this:

Import job failed: 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 (3346, en, VARGRP536_Z17040045). The statement has been terminated. Sql query: update [dbo].[EcomProducts] set [ProductStock]=[dbo].[EcomProductsTempTableForBulkImport1].[ProductStock], [ProductLanguageId]=[dbo].[EcomProductsTempTableForBulkImport1].[ProductLanguageId] from [dbo].[EcomProductsTempTableForBulkImport1] where [dbo].[EcomProducts].[ProductNumber]=[dbo].[EcomProductsTempTableForBulkImport1].[ProductNumber] ;

That makes no sense to me. Why is it doing an insert and not an update? I would expect a query like:

UPDATE EcomProducts SET ProductStock = 22 WHERE ProductNumber = 'Z17040045'

Why is the Dynamicweb provider now also trying to do Ecom-related work like fetching product ID and variant IDs?

And what would be the approach I should follow?

Thanks!

Imar

Note: running on 9.16.6.

Note 2: it works when choosing the lower level SQL Provider. Just a little worried what happens when it breaks there in the future as I then run out of providers :-)

Hello Imar,

- you could solve it by adding a table mapping for each language ('en', 'en-GB' and 'da') just as I have done on the EcomProducts table below. In my case I want to have EAN (ProductEAN) updated on all languages, where the product exist.

Br. Michael Knudsen

Hi Michael,

Yeah, that works, and we use that to set up the initial creation of products across languages. But for something simple as stock or an EAN, it sucks :-) First of all, it means setting up the same mapping multiple times. With just a product number and stock / EAN that's not too bad. But this becomes really nasty when you're updating, say, 50 language-independent fields in 12 languages as it involves a lot of manual work. Secondly, new languages added will require manual updates of existing jobs. 

This used to work just fine with the Dynamicweb Provider, but it looks like somehow it inherited some Ecom Provider smarts to understand what a product is, instead of dumping data directly in the EcomProducts table. For the Ecom Provider I can understand this behavior, but for the Dynamicweb Provider I am not convinced it's a good solution. Also, the SQL Provider isn't a great option either until it gets a "Use current database" option for the connection. Without that, the jobs aren't transferable between environments as they will point to whatever server and database is stored in the job.

So, something that used to be a 5-minute job, now suddenly becomes ten-fold that + future maintenance, or messy scripting in deployment pipelines :-(

Thanks,

Imar

Thanks Dmitriy, setting the language to none did the trick (I already had the ProductNumber as a key).

Has it always been like that? I have an older solution running 9.12 where it works with the language set to somehting instead of None. However, that uses a Product ID and Variant ID instead of just the number so maybe it's different there.

Imar

I see. Thanks for the insight; that helped a lot!