Integration between SugarCRM OnPremises and Microsoft Dynamics 365 Online

The following diagram depicts the flow of accounts and contact from SugarCRM to Microsoft Dynamics:

Locating the Sample Template

The Sample template is shipped with the Spectrum Technology Platform installer zip. Depending on your installation directory, you can find the sample template inside Program Files\Pitney Bowes\Spectrum\server\modules\metadata-insights\connectors\samples\MS_Dynamics_365_SugarCRM__Accounts_Sync_Dataflow. The folder contains all the required files to configure the data flow. Following table lists the files contained in the folder:

Prerequisites

You need to create two connections to import and deploy required files to configure the data flow. Follow these steps to create connections:

1. Install the latest Spectrum platform Server
2. Open the Spectrum Server home page
3. Click Platform Client Tools and then click Web
4. Open the Management Console view
5. Click Resources menu and then click Data Sources and create two connections:
   1. SugarCRM_TestConnection with Type SugarCRM. Click Test and a success message is displayed: The connection SugarCRM_OnPremises successfully connected to the data source
   2. MSDynamics_TestConnection with Type Microsoft Dynamics 365. Click Test and a success message is displayed: Success: The connection Microsoft Dynamics 365 Online successfully connected to the data source
   For more details on how to create connections, refer to Connecting to SugarCRM and Connecting to Microsoft Dynamics 365 Online.

Importing and Deploying Files to the Server

With connection in place, now you need a command line utility to import and deploy the files to the server.

1. Open the Spectrum platform home page, click Platform Client Tools followed by Command Line and download the following files:
   1. Job Executor, which is an individual .jar file
   2. Administration Utility, which is a zipped file (spectrum-cli)
2. Unzip the spectrum-cli file
3. Copy all the files from the Sample Template folder to the spectrum-cli folder. This saves you from entering full path of every file with each command
4. Run cli.cmd utility located in the spectrum-cli-12.1 folder. The command line interface of the Spectrum Platform is launched
5. Connect to the Spectrum Server using following commands: connect <server name>:<port> --u <username> --p <password>. Successful connection is confirmed by message: Connected to server<server name> :< port>
6. Type and execute the following commands in the given order to import and deploy the sample data flows:
   1. Import modelstore using command: spectrum> modelstore bulkImport --importDependency true This command imports the modelstore with all the dependencies.
   2. Deploy modelstore using command: modelstore deploy --n <name>
   3. Import dbconnection using command: dbconnection import --f <name.json>
   4. Import data flows using command: dataflow import --f <name.df>

Viewing Imported Files

After successful import, you can use these data flows to demonstrate the Spectrum capabilities. Files are stored at following locations:

1. Modelstore: In the Physical Model and Model Store tabs in Metadata Insights
2. Dataflow: In the Server Explorer of Enterprise Designer application
3. Json: In the Model Store in Management Console

Executing the migration of data

1. Launch the Enterprise Designer application. You can download the executable setup of this application from Desktop section of Platform Client Tool on the Spectrum Platform home page
2. Login using your Spectrum credentials
3. Click View from the menu and then click Server Explorer
4. Double-click the SugarCRMAccount_Sync_MSDAccount dataflow job first
5. Double-click Read from DB_SugarCRM stage
   1. Change the value of field date_entered as required and click OK

      Select "SugarCRM_PM"."Accounts"."email1", "SugarCRM_PM"."Accounts"."name",
      "SugarCRM_PM"."Accounts"."phone_office", "SugarCRM_PM"."Accounts"."date_entered" From
       "SugarCRM_PM"."Accounts" Where "SugarCRM_PM"."Accounts"."date_entered" Like '2017-08-28%'

   2. Click OK to continue
6. Run the flow by clicking Run button on the Tool Bar in Enterprise Designer
7. Visit the MS Dynamics home page and click Accounts under Customers tab on the Sales page. Notice that the Accounts have been migrated
8. Double-click the SugarCRMContact_Sync_MDSContacts job
9. Double-click Read from_MSDynamics_DB stage
   1. Change the value of the field createdon as required and click OK

      Select "MSDynamics_PM"."account"."name", "MSDynamics_PM"."account"."telephone1",
       "MSDynamics_PM"."account"."emailaddress1", "MSDynamics_PM"."account"."createdon",
       "MSDynamics_PM"."account"."accountid" From "MSDynamics_PM"."account"
       Where "MSDynamics_PM"."account"."createdon" Like '2017-09-11%'

   2. Click OK to continue
10. Run the flow by clicking Run button on the Tool Bar in Enterprise Designer
11. Visit the MS Dynamics home page and click Contacts under Customers tab on the Sales page. Notice that the Contacts are listed on the page