Developer forum

Forum » Integration » EcomProvider - Importing related products that are variants

EcomProvider - Importing related products that are variants

Christian Fisker
Reply

Hi,

I am trying to import related products, where the related products can be variants.

The source XML contains fields for ProductRelatedProductRelID and ProductRelatedProductRelVariantID, but EcomProvider is not allowing me to map the ProductRelatedProductRelVariantID column. This results in a primary key violation when data is inserted in the EcomProductsRelated table. The column ProductRelatedProductRelVariantID is part of the primary key of that table!

This is a snippet of the data to be imported:

 

This is the attempted mapping

And the key columns for the mapping

I am using the newest version of Dynamicweb.DataIntegrations.Providers.EcomProvider (5.0.13)

Thanks

/Christian

 

 


Replies

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Reply

Hi Christian
maybe the problem is that your source xml provider is not seeing the latest changes made in the source xml file where it now contains the new ProductRelatedProductRelVariantID column added. So you can try to create a new job with the same mappings and settings
and then the new column will be available in the mappings list.
BR, Dmitrij

 
Christian Fisker
Reply

Thank Dmitriy.

The field is now mapped in the job, but I am stil getting primary key violation, when the job is copying data from the bulk temp-table to EcomProductsRelated - RelVariantID is not part of the copied data.

insert into EcomProductsRelated (ProductRelatedProductID,ProductRelatedProductRelID,ProductRelatedGroupID) select ProductRelatedProductID,ProductRelatedProductRelID,ProductRelatedGroupID from EcomProductsRelatedTempTableForBulkImport3; 

Here's the full error:

Violation of PRIMARY KEY constraint 'PK_EcomProductsRelated_1'. 
Cannot insert duplicate key in object 'dbo.EcomProductsRelated'. The duplicate key value is (2444589064030, 811139021812-main, RELGRP4, ). The statement has been terminated. Sql query: 
delete t1 from EcomGroupProductRelation t1 INNER JOIN ecomgroupproductrelationTempTableForBulkImport t2 ON t1.GroupProductRelationGroupID = t2.GroupProductRelationGroupID AND t1.GroupProductRelationProductID = t2.GroupProductRelationProductID;
insert into EcomGroupProductRelation (GroupProductRelationGroupID,GroupProductRelationProductID,GroupProductRelationSorting,GroupProductRelationIsPrimary) select GroupProductRelationGroupId, GroupProductRelationProductID,GroupProductRelationSorting,GroupProductRelationIsPrimary from ecomgroupproductrelationTempTableForBulkImport;
delete from EcomShopGroupRelation where ShopGroupGroupID in (select ShopGroupGroupID from EcomShopGroupRelationTempTableForBulkImport);
insert into EcomShopGroupRelation(ShopGroupShopID,ShopGroupGroupID,ShopGroupRelationsSorting) select shopgroupshopid,shopgroupgroupid,ShopGroupRelationsSorting from ecomshopgrouprelationtemptableforbulkimport;
delete from EcomVariantgroupProductRelation where VariantgroupProductRelationProductID in (select VariantgroupProductRelationProductID from EcomVariantgroupProductRelationTempTableForBulkImport); 
insert into EcomVariantgroupProductRelation (VariantgroupProductRelationID,VariantgroupProductRelationProductID,VariantgroupProductRelationVariantGroupID)select VariantgroupProductRelationID,VariantgroupProductRelationProductID,VariantgroupProductRelationVariantGroupID from EcomVariantgroupProductRelationTempTableForBulkImport;
delete t1 from EcomVariantOptionsProductRelation t1 INNER JOIN EcomVariantOptionsProductRelationTempTableForBulkImport t2 ON t1.VariantOptionsProductRelationProductId = t2.VariantOptionsProductRelationProductId AND t1.VariantOptionsProductRelationVariantId = t2.VariantOptionsProductRelationVariantId;insert into EcomVariantOptionsProductRelation (VariantOptionsProductRelationProductID,VariantOptionsProductRelationVariantID)select VariantOptionsProductRelationProductID,VariantOptionsProductRelationVariantID from EcomVariantOptionsProductRelationTempTableForBulkImport;
insert into EcomShops (ShopID,ShopName) select shopid,shopname from EcomShopsTempTableForBulkImport; insert into EcomProductsRelatedGroups (RelatedGroupID,RelatedGroupName,RelatedGroupLanguageID) select RelatedGroupID,RelatedGroupName,RelatedGroupLanguageID from EcomProductsRelatedGroupsTempTableForBulkImport; 
delete from related from EcomProductsRelated related where ProductRelatedProductID in (select ProductRelatedProductID from EcomProductsRelatedTempTableForBulkImport3 inside WHERE related.ProductRelatedProductID = inside.ProductRelatedProductID AND related.ProductRelatedProductRelID = inside.ProductRelatedProductRelID AND related.ProductRelatedGroupID = inside.ProductRelatedGroupID); 
insert into EcomProductsRelated (ProductRelatedProductID,ProductRelatedProductRelID,ProductRelatedGroupID) select ProductRelatedProductID,ProductRelatedProductRelID,ProductRelatedGroupID from EcomProductsRelatedTempTableForBulkImport3; 
delete from EcomGroupRelations where groupRelationsGroupID in (select groupRelationsGroupID from EcomGroupRelationsTempTableForBulkImport);
insert into EcomGroupRelations (GroupRelationsGroupID,GroupRelationsParentID,GroupRelationsSorting) select GroupRelationsGroupID,GroupRelationsParentID,GroupRelationsSorting from EcomGroupRelationsTempTableForBulkImport;

BR,
Christian

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Reply

Hi Christian,
that is a bug and will be fixed in the next EcomProvider 5.0.14 version.
Meanwhile you can download the current version with a fix.
BR, Dmitrij

 
Christian Fisker
Reply

Thanks Dmitriy,

I tried it out but I am now seeing another error. I did create a brand new integration job with all default settings. Does version 5.0.14 have a dependency on something else that is new?

Job Failed with the following message: Method not found: 'System.String Dynamicweb.DataIntegration.Integration.ColumnMapping.GetScriptValue()'. The failed input row is: [GroupId:"93923"], [GroupLanguageId:"LANG18"], [GroupName:"Odes"], [GroupSmallImage:"0d6fcaef-19cd-4139-be82-b264711e16ab"], [GroupLargeImage:"0d6fcaef-19cd-4139-be82-b264711e16ab"], [GroupDescription:"..."], [GroupNavigationShowInMenu:"True"], [GroupMetaTitle:"..."], [GroupMetaKeywords:""], [GroupMetaDescription:"..."], [Shops:"SHOP1"], [ParentGroupsSorting:"3"], [ParentGroups:"93921"], [PerfionReports:""], [WebMenuTitle:""]

BR,
Christian

 

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Reply

Yes, also download the latest version of DataIntegration package

 
Christian Fisker
Reply

Updating to the latest version of Dynamicweb.DataIntegration requires a lot of other updates. That is not something we are prepared to risk at this time.

Any way we can get a fix only for the EcomProvider (version 5.0.13.1)?

BR,
 Christian

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Reply

ok, can you try with 3.0.11 of DataIntegration package then?

 
Christian Fisker
Reply

We are currently on version 3.0.19 (DW 9.12.0)

 
Dmitriy Benyuk Dynamicweb Employee
Dmitriy Benyuk
Reply

Correct, 3.0.11 is too old, try with 3.0.29

 

You must be logged in to post in the forum