Scribe Online Tutorials ( TIBCO Cloud Integration – connect azure or aws) Part 4/4 – Integration Solution , Mapping and Flow – Microsoft dynamics 365 CRM to On-premise SQL Server sync integration

Our free trial is successfully activated and we will find 1 default agent. There are many limitations with the Trail version which we will check while installing connector and creating Microsoft dynamics connection.

We installed MICROSOFT DYNAMICS 365 CRM Connector and created a connection in TIBCO cloud integration – Connect (Scribe Online)

we Installed On-Premise Agent, connector and created a connection with on-premise SQL server agent in TIBCO cloud integration (scribe Online)

Now we will create Integration Solution/ Project for Account and contact and Mapping from Microsoft dynamics 365 to SQL Account Table and test Sync from Microsoft dynamics 365 to on- Premise SQL

This post is divided into below points

1. Create an Integration Solution app, On Event App, Scheduled, Replication
2. Create new Mapping Flow for Account with status approved
3. Validate, Get Record, get ist records
4. Test the Sync, when the account is approved in Dynamics 365 CRM sync it to SQL account table

There are 3 Type of connect app

1. On Event Apps
2. Data Replication Apps
3. On Schedule apps

1. On Event Apps: by Name itself its clear when starting the sync when an event occurs from the source. i.e Account status changed to approved, then only trigger the Sync.
   Now Question arises, we have just made a Connection, no event component was imported in Mscrm so how it will know about the actionSo Answer is Events need to be created inside Dynamics 365 CRM Example Create a Power automate flow, Plugin or webhook that will use Endpoint provided by the mapping flow.As below we can see the Endpoint provided when creating and Reply and request flow.

   We will create another post to create an event and trigger scribe flow on event : Comming Soon

   

2. Data Replication: This type is normally used for Bulk Migration where our bulk batch and there’s are used, we can use ODBC for any legacy system data too Microsoft dynamics 365 or even any Excel we have in system with million of data we can use ODBC connections

   

3. On Schedule Apps: Use an On schedule app to integrate data between various online and on-premise data stores by mapping source and target datastores and then running your apps on a specified schedule, such as every hour, once a day, specific days of the month, or on-demand.
   Select Apps from the menu to open the apps list.
   Select an existing On schedule app to edit it or select the Create/Import button and choose On schedule to add a new app.

   

    

1. On the Flows tab, configure one or more flows to accomplish your integration goals.
   • To create or edit a flow
2. On the Connections tab, view a list of the Connections used by the flows in this app.
3. Select the Schedule tab.
   Select the method that triggers the app to run. Options include:
   • On-Demand — App is run manually.
   • Scheduled — App is run at scheduled intervals.
4. In the Execution History tab, Displays up to 45 days of Execution History for this app.
5. Now Click on New Flow to create and mapping flow.

   

6. Give the name for the flow ” Account Sync to SQLACC table flow”, I am starting with querying the account table

   

7. Now navigate to filter and provide necessary filters for the query, I used if Status Reason = approved

   

8. Once done add ”  For Each Result “action, this action will act as for each look for each record in the result.
9. Then add “Create” Action from SQL in for each record, now there might be need of fetching lookup optionset label , Update and Insert etc. which we will cover in another post, i.e How to fetch record, lookup, optionset , execute et.c in TIBCO scribe online.

   

   

10. Clicking on the field from the above screen and here we will see source and destination fields, simply drag and drop the field from source connection to map.
    If Field names and data types are similar to most of the fields, we can leverage the option of automap or auto-link,
    Clicking on Validate will provide info Error/Warning i.e if we missing some mandatory field or mapped wrong data type.
    I have mapped a few mandatory fields just for the testing

    

11. Once Done, Validate and Save the mapping and Integration solution App
12. After completing Mapping, we can 3 Option in Scheduling
    On-Demand — App is run manually.
    Scheduled Recurring — App is run at scheduled intervals.
    One time– Run only once
13.  We will select On-demand for the testing purpose.

    

14. Now we will test the sync

1. We will pick an Account and change status to approved

   

2.  Coming back to Scribe and running our Sync,
   Remember to check Mapping should be enabled and Agent should be Running
   once On-Demand Run is processed and completed we can check the status in Execution History

   

   Below is the example where we got the Error, easily viewed under Error Details

   

3. Sync Successful with Second attempt after rectifying the issue.