LibrarySites.Banner

Catalog Synchronization from Microsoft Dynamics

NOTE: This doc was written with SCpbMD8.0 initial release. There are some changes with the update1 release and I will post a new one for it later.

Here I want talk about how to synchronize catalog from Dynamics to Sitecore Commerce. There are some high level information available on Sitecore Dev portal and also different ways to sync catalog between AX and Sitecore Commerce - such as Sitecore tasks, Windows Services etc. 

This post is focusing on use the routing console application (provided as SDK) to do product sync and provide steps you need to follow if you want do by yourself. 

Configure Routing Console

Routing console application is the command line based tool which talks to both Dynamics AX and Sitecore Commerce and synchronize catalog data (one way always from AX to Sitecore Commerce)

  1. Download the “Rotuing.Framework SDK” from Sitecore Dev Portal: https://dev.sitecore.net/~/media/Downloads/Sitecore_Commerce_powered_by_Microsoft_Dynamics/8,-d-,0/Sitecore_Commerce_powered_by_Microsoft_Dynamics_8/Non-secure/Routing,-d-,Framework,-d-,Sdk,-d-,8,-d-,0,-d-,114,-d-,0.ashx
  2. Follow the readme.txt (mainly to resolve missing reference) inside to build the SDK solution. The specific tool we need for below process is “Routing.Console” project which should build Sitecore.Commerce.DynamicsRetail.Synchronization.Console.exe
  3. Locate the Routing.Framework.Sdk.8.0.114.0.zip\Routing.Framework.Sdk\Binaries\Release\RoutingConsole\ Sitecore.Commerce.DynamicsRetail.Synchronization.Console.exe.config file:

  • Change below according to your Sitecore instance information:

<appSettings>

<add key="Host" value="storefront" />

<add key="UserName" value="sitecore\admin" />

<add key ="Password" value="b" />

<add key="SitecoreDatabase" value="master" />

</appSettings>

The “Host” value is the Sitecore instance hostname and it needs to be different than the IIS application name assigned to Sitecore instance or, sometimes, it needs to be a FQDN. Otherwise later steps won't work. A good verification is to see if this URL renders back a JSON response: 

http://storefront/-/item/v1/?sc_database=web&sc_itemversion=1&language=default&payload=Content&scope=c&sc_itemid=06A9A56D-D6B3-413C-A2BE-412C0E53CD11

  • Change below according to your Sitecore Commerce catalog web service information. Basically the console app is using this web service to import catalog data into Sitecore Commerce:

<client>

<endpoint address="http://localhost:2000/SolutionStorefrontSite_CatalogWebService/CatalogWebService.asmx" binding="basicHttpBinding" bindingConfiguration="CatalogWebServiceSoap" contract="Sitecore.Commerce.Catalog.CatalogWebServiceSoap" name="CatalogWebServiceSoap"/>

</client>

Note: windows authentication is required to access Sitecore Commerce catalog web service

Configure Sitecore Commerce

Console app needs to access Sitecore tree to retrieve information about Dynamics AX (server name, ChannelID, ChannelDB etc.), routing information and log locations etc.

  1. Assuming the Sitecore, Sitecore Commerce (SCpbCS) and needed Sitecore packaged are installed and deployed as required by the step 1-12 from the SCpbMD deploy doc
  2. Open Content Editor and edit the following items. Save and publish all item changes.
  3. Open /sitecore/Commerce/Commerce Channels/Contoso
    1. Ensure the sync token is cleared
    2. Open /sitecore/Commerce/Commerce Synchronization/Commerce Endpoints/Endpoint_SitecoreWebApi_Configuration_Dev
      1. Ensure username and password is specified
      2. Ensure "Enabled" checkbox is checked
      3. Open /sitecore/Commerce/Commerce Synchronization/Commerce Routes/Route Requests/Contoso Commerce Route Request
        • Ensure approved checkbox is checked
      4. Open /sitecore/Commerce/Dynamics Routing Extensions/Commerce Endpoints/Azure Contoso Commerce Endpoint - Dynamics Retail Crt
        • Ensure server is specified (the value should be the AX instance URL which needs to be accessible from where the routing console application is running)
        • Ensure database is specified (the AX channel database name. eg retailcontosostore)
        • If Integration windows authentication is used, ensure “IsTrusted” is checked
        • Ensure port is specified (the AX channel database port. eg 57500)
        • Ensure username and password are specified (Only needed if the SQL authentication is being used to access AX channel database.)
        • Ensure enabled checkedbox is checked
      5. Disable Sync Task which is another sync mechanism based on Sitecore scheduled task. Since we are using the routing console to do the On Demand sync, we can disable the Sync task to avoid conflicts
        • Open /sitecore/system/Tasks/Schedules/Commerce Synchronization Schedule
        • Ensure "Schedule" property is changed to a date that that would ensure it is not run (eg change 2014 to 2016)
      6. By default, when console app runs, it first outputs catalog XML file from AX then imports it into Sitecore Commerce. There are situations you want import generated catalog XML into Sitecore commerce at later stage, you can disable it by follow below:
        1. Uncheck the "Enabled" for /sitecore/Commerce/Dynamics Routing Extensions/Dynamics Routes/Route_SyncSitecoreCommerceChannel_Contoso_AzureAxVm/Step_PublishExtract

Execute Routing console app

  1. There are some extra dependency required at runtime even they are not required to build the console application. The simplest way to work around the dependency issue is:
    • Copy the Sitecore.Commerce.DynamicsRetail.Synchronization.Console.exe, Sitecore.Commerce.DynamicsRetail.Synchronization.Console.config and commerceRuntim.config files from your SDK project output folder (e.g. Routing.Framework.Sdk\Binaries\Debug\RoutingConsole) to your Sitecore bin folder
    • Also copy the Templates folder, which is deployed by DynamicsRetail.Routing.8.0.116.0.update package, from your Sitecore "website" folder to Sitecore bin folder 
    • Run Sitecore.Commerce.DynamicsRetail.Synchronization.Console.exe from Sitecore bin folder
  2. It should output some information in the console window and eventually complete
  3. If it didn't work take a look at /sitecore/Commerce/Commerce Channels/Contoso/Channel History or /sitecore/Commerce/Dynamics Routing Extensions/Dynamics Routes/Route_SyncSitecoreCommerceChannel_Contoso_AzureAxVm/Route History for the latest history file. The Tracking Log property will display log output
  4. If there are no errors from console output but no catalog XML generated, you may want to clear the "Last Run" and "Requested At" values and set "Repeat Delay" to a smaller value (e.g. 2 instead of default 120) under /sitecore/Commerce/Commerce Synchronization/Commerce Routes/Route Requests/Contoso Commerce Route Request
  5. Once the execution is completed, the catalog XML should be generated under “c:\Sitecore Synchronization Data\Working\Contoso_Dev\”. This location is defined by /sitecore/Commerce/Dynamics Routing Extensions/Commerce Endpoints/Endpoint_FileSystem_Contoso_Dev_Working (or _Archive)
  6. If the Step_PublishExtract is enabled (default is enabled), the catalog data should be imported. You can validate this in catalog manager by looking at the import status/history

Dynamics AX setting

  1. The above process is verified with Microsoft Dynamics AX 2012 R3 with Cumulative Update 8 which is also the minimal AX version required by SCpbMD.
  2. The test AX VM is created from the LCS DEMO template in Azure environments.
  3. The channel database is with default integrated windows authentication

Note

  1. It is possible to run the Routing Console app off the Sitecore Commerce box – for example running it on AX box – as long as it can access the Sitecore instance (defined in Sitecore.Commerce.DynamicsRetail.Synchronization.Console.exe.config file) and AX instance URL (defined in Sitecore tree under /sitecore/Commerce/Dynamics Routing Extensions/Commerce Endpoints/Azure Contoso Commerce Endpoint - Dynamics Retail Crt) at the same time
  2. The routing console application can access AX channel database either using integrated windows or SQL authentication depending on the setting under /sitecore/Commerce/Dynamics Routing Extensions/Commerce Endpoints/Azure Contoso Commerce Endpoint - Dynamics Retail Crt
  3. By default, the routing framework is configured to access “Contoso online store” (with “channel ID” 5637144608 and “Organizational Unit” 068). If you want to access different AX channel/retailStore, you can switch to a different AX channel by adjusting the values for "ExternalChannelID" and "Organizational Unit" fields of /sitecore/Commerce/Commerce Channels/Contoso node

More updates:

May 5 2015:

"Organization Unit" value


The "Organization Unit" value is not being used for console application (while the transaction service still uses it but it gets its value from a different location). It actually may cause errors like below:
[MapToChannel] Method 'Sitecore.Commerce.Routing.Models.CommerceChannel.OrganizationalUnit' not found.

So I suggest to remove this item from Sitecore tree by doing below:

  1. In your Sitecore Content editor, navigate to this path:  /sitecore/templates/Commerce/Commerce Extensions/Commerce Channels/Channel Dynamics Retail
  2. Delete the "Organizational Unit" filed. You can do this by highlight the field first, then in Builder/Option tab, click "Remove" button
  3. Save the change and verify if this field is removed from /Sitecore/Sitecore Commerce/Commerce Channels/Contoso

SQL vs Windows Authentication

I mentioned above the routing console supports either Integrated windows or SQL authentication when it connects to AX channel DB. However, OOB the AX channel DB is only configured to support windows auth. So unless you are going to make changes in AX - likely won't be supported by MSFT-  SQL auth will not be working when routing console talks to AX. Therefore it is recommended (and supported) to configure routing console to use windows auth (in Sitecore tree).

One of the requirement to use windows auth is to put routing console machine and AX channel DB in the same domain (otherwise it needs some kind of domain federation mechanism). If your AX and Sitecore machines are on different domains, you can achieve this using AX CDX. In that case, the AX channel DB can be located in different domain, e.g. your Sitecore domain, and CDX is responsible to sync data between AX headquarter and channel DB. The detail configuration on CDX is outside the scope of this blog.

CDX realtime service

It is not obvious but the CDX realtime service needs to be invoked by routing console to update the channel publish status. Therefore,. if you are getting below exception, you may want to check if your RealtimeService is running on AX side:

[Exception] Exception while calling invoke method UpdateChannelPublishingStatus: There was no endpoint listening at https://retail.contoso.com:84/RealTimeService/Service.svc/Common that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.<br/>

  • RE >> Ensure port is specified (the AX channel database port. eg 57500)  Why port 57500, isn't the channel database a standard SQL database and therefore should use port 1433?  Or is this port in order to talk to the Async client?

  • I'm copying Hao Liu's answer from the Sitecore developers support forum:   A quick answer on the SQL port number. 1433 normally is the local SQL port while 57500 is the default port for Azure hosted VM (MSFT LCS provides AX VM hosted in Azure) when you connect its SQL remotely. It's really depending how you map your remote/local SQL port for Azure VM, so it can be values different than 57500.