Hello,
I have been working with the Endpoint and OData providers creating data integration jobs from Custom API pages in BC. Currently I am working on a products import and am running into a data mismatch error.
Import job failed: cannot parse '125' to a double. Table: EcomProducts. Column: ProductPrice The failed input row is: [@odata.etag:"W/"JzQ0O29heDNOeERKR0RRQVMyQ2M1ODI1cmlvQ2h5SlZOaWJ1TVNQMldPL0duTUE9MTswMDsn""], [id:"496884fd-a35a-eb11-89f9-000d3a10d7b2"], [productId:"10000500303018"], [productBrandCode:"10018"], [productBrandName:"Chateau du Moulin-a-Vent"], [vintageCode:"2018"], [casePack:"3"], [alcoholTypeCode:"WINE"], [alcoholContentPercent:"13.5"], [proof:"27"], [fullCasePurchaseOnly:"True"], [salesOrganizationCode:"6005"], [salesOrganizationName:"National Division Post"], [productName:"MOULIN-A-VENT 18 MOULIN-A-VENT"], [productShortDescription:""], [ProductGenPostingGroup:"DOMESTIC"], [ProductInventoryPostingGroup:"FINISHED GOODS"], [CountryRegionOriginCode:"FR"], [productActive:"True"], [type:"Inventory"], [itemCategoryId:"9bb0acc9-ef8f-ea11-a813-000d3a4e631c"], [itemCategoryCode:"GAMAY NOIR"], [varietal1:"Gamay Noir"], [varietal2:"JG"], [blocked:"False"], [gtin:"098GTIN098987"], [productStock:"75"], [unitPrice:"40"], [priceIncludesTax:"False"], [unitCost:"23.528"], [taxGroupId:"d967f92f-ec8f-ea11-a813-000d3a4e631c"], [productTaxGroupCode:"TAXABLE"], [productDefaultUnitId:"CS"], [productPrice:"125"], [productWeight:"0"], [productVolume:"0"], [producHeight:"0"], [productWidth:"0"], [productLength:"0"], [lastModifiedDateTime:"5/21/2021 3:44:12 PM"]
The row that is currently failing is productPrice which is mapped to 'ProductPrice' on the EcomProducts table. The productPrice field in the Odata json response is declared as decimal in the metadata response for the Endpoint provider: <Property Name="productPrice" Type="Edm.Decimal" Scale="Variable" />
Following this I took the OData response that is being using in the endpoint/odata data integration job (see attached) and editted the format so that I could create a job that uses the JSON source provider to troubleshoot. When I set up this new JSON based data integration job I ran into a slighty different, but similar error:
Unable to cast object of type 'System.Int64' to type 'System.String'. The failed input row is: [@odata.etag:"W/"JzQ0O29heDNOeERKR0RRQVMyQ2M1ODI1cmlvQ2h5SlZOaWJ1TVNQMldPL0duTUE9MTswMDsn""], [id:"496884fd-a35a-eb11-89f9-000d3a10d7b2"], [productId:"10000500303018"], [productBrandCode:"10018"], [productBrandName:"Chateau du Moulin-a-Vent"], [vintageCode:"2018"], [casePack:"3"], [alcoholTypeCode:"WINE"], [alcoholContentPercent:"13.5"], [proof:"27"], [fullCasePurchaseOnly:"True"], [salesOrganizationCode:"6005"], [salesOrganizationName:"National Division Post"], [productName:"MOULIN-A-VENT 18 MOULIN-A-VENT"], [productShortDescription:""], [ProductGenPostingGroup:"DOMESTIC"], [ProductInventoryPostingGroup:"FINISHED GOODS"], [CountryRegionOriginCode:"FR"], [productActive:"True"], [type:"Inventory"], [itemCategoryId:"9bb0acc9-ef8f-ea11-a813-000d3a4e631c"], [itemCategoryCode:"GAMAY NOIR"], [varietal1:"Gamay Noir"], [varietal2:"JG"], [blocked:"False"], [gtin:"098GTIN098987"], [productStock:"75"], [unitPrice:"40"], [priceIncludesTax:"False"], [unitCost:"23.528"], [taxGroupId:"d967f92f-ec8f-ea11-a813-000d3a4e631c"], [productTaxGroupCode:"TAXABLE"], [productDefaultUnitId:"CS"], [productPrice:"125"], [productWeight:"0"], [productVolume:"0"], [producHeight:"0"], [productWidth:"0"], [productLength:"0"], [lastModifiedDateTime:"5/21/2021 3:44:12 PM"]
I was able to narrow this down to any of the integer value field in the JSON such as productPrice or productStock by removing all mappings except the primarykey, in this case productId, and either of those fields.
At this point I am looking for any advice on how to proceed or further troubleshooting recommendations.
Thanks,
Jeremy Fesmire
