Ensuring Your Dynamics 365 on Premise to Cloud Migration Doesn’t Destroy the Integrations You Need

The famous Adventure Works DW (Datawarehouse) database from Microsoft (download available at the end), is going to serve as our base for this demonstration. We’ll showcase how seamless it is to integrate your existing processes with Microsoft Dynamics 365 Online using the Connect Bridge Platform.

In the following procedure, we will use SSMS (SQL Server Management Studio) to manage your SQL Server Database:

1 - Once you set up the AdventureWorks database, you will see the schema of the database in the Object Explorer window, as in the following image.

By expanding the catalogs node in the TreeView, we can see the entire schema provided by Dynamics 365, just as you would see by accessing a regular database. But this is, in fact, an abstraction with API calls running in the background to make it this simple in the front end.

Using this 3-step procedure will allow you to create any kind of integration and/or customize existing processes. Let’s take a look at what users around the world use this for.

In case you are wondering why you would ever need to do this in the first place, here are two common scenarios that may hit close to home:

Scenario 1

The company you work for still uses Microsoft Dynamics CRM on-premises, and they are currently in the process of migrating to Microsoft Dynamics 365 Online. They have several integrations between from their SQL server databases to their Microsoft Dynamics CRM on-premises.

Problem is, you can’t connect with Microsoft Dynamics 365 Online database directly, and as a result, you start to wonder whether:

• You will have to create, from scratch, all the processes using the Dynamics CRM SDK from Microsoft;
• You will have to create, from scratch, all of the processes using the Web API;
• You will have to learn C# in order to do all this;

Scenario 2

The company you work for has a custom-made ERP System with a SQL Server database as back-end. They decide to use Microsoft Dynamics 365 CRM as their own Customer Relationship Management System.

Then, they would like to have the ability to populate the CRM with existing data from their ERP System in an automated way and keep both sides consistent.

Doing this process manually is practically impossible since there are tens of thousands of records that need to be identical on both sides, including accounts, contacts, and products.

However, the company’s IT team does not have much experience with the necessary programming languages and frameworks, such as C#, REST services or the CRM API. They do have experience with SQL, but can that be of use? The manager wonders if they:

• Will have to hire a consultancy company to perform this service for them.
• Will depend on the said consultancy company to maintain and add new features as their business expands.

Thankfully, the answer to all these hypothetical questions is no, and hopefully, at this point, the use of the example above is ringing a bell.

With Connect Bridge, you can leverage your existing skills in a efficient way AND keep your existing software, databases and processes.

But you don’t have to take our word for it. Check out the step-by-step example below, or, even better, ask for your free trial and give it a try yourself.

Preparation

1. Create the following triggers:

--*****************************************************************************

CREATE TRIGGER [dbo].[OnDimCustomerInsert] ON [dbo].[DimCustomer] AFTER INSERT

AS

BEGIN

       SET                               NOCOUNT ON

 

       DECLARE @SQL                      VARCHAR(255)

       DECLARE @AccountId                VARCHAR(255)

       DECLARE @FirstName                VARCHAR(255)

       DECLARE @LastName                 VARCHAR(255)

       DECLARE @MiddleName               VARCHAR(255)

      

       SELECT @FirstName       = [FirstName],

              @LastName        = [LastName],

              @MiddleName      = [MiddleName]

       FROM INSERTED;

       SELECT @SQL = FORMATMESSAGE('INSERT INTO ACCOUNT (Name) VALUES(''%s''); SELECT SCOPE_IDENTITY();',

       @FirstName + '-' + '-' + @LastName)

      

       CREATE TABLE #AccountId(ID VARCHAR(255))

       INSERT #AccountId

       EXEC(@SQL) AT D365_CRM

 

       SELECT @AccountId = ID from #AccountId

       UPDATE DimCustomer SET CustomerAlternateKey = @AccountId where FirstName = @FirstName and MiddleName = @MiddleName and LastName = @LastName

 

       PRINT @FirstName + ' ' + @MiddleName + ' ' + @LastName;

END

--*****************************************************************************

CREATE TRIGGER [dbo].[OnDimCustomerUpdate] ON [dbo].[DimCustomer] AFTER UPDATE

AS

BEGIN

       SET                               NOCOUNT ON

 

       DECLARE @SQL                      VARCHAR(255)

       DECLARE @AccountId                VARCHAR(255)

       DECLARE @FirstName                VARCHAR(255)

       DECLARE @LastName                 VARCHAR(255)

       DECLARE @MiddleName               VARCHAR(255)

      

       SELECT @AccountId       = [CustomerAlternateKey],

              @FirstName       = [FirstName],

              @LastName        = [LastName],

              @MiddleName      = [MiddleName]

       FROM INSERTED;

 

       SELECT @SQL = FORMATMESSAGE('UPDATE ACCOUNT SET Name=''%s'' WHERE ACCOUNTID=''%s''', @FirstName + '-' + '-' + @LastName, @AccountId)

       EXEC(@SQL) AT D365_CRM;

       PRINT @FirstName + ' ' + @MiddleName + ' ' + @LastName;

 

END

3 -Open your Dynamics 365 Online instance and navigate to the Accounts. We will insert 3 new records for ‘ABC Technology’, ‘Billy Charity’, and ‘Tom Cat’ on the next step. At this point, they are still not in Accounts.

Testing

 1 - In SSMS open a new query window and paste the following:

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('ABC Technology', '' ,'GMB', 'CT0001');

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('Billy Charity', '' ,'SA', 'CT0002');

INSERT INTO [AdventureWorksDW].[dbo].[DimCustomer] (FirstName, MiddleName, LastName, CustomerAlternateKey) values ('Tom Cat', '' ,'ORG', 'CT0003');

 

SELECT * FROM DimCustomer WHERE FirstName = 'ABC Technology';

SELECT * FROM DimCustomer WHERE FirstName = 'Billy Charity';

SELECT * FROM DimCustomer WHERE FirstName = 'Tom Cat';

2 - Run the scripts by pressing F5 or the Execute button in SSMS.

You should have now 3 additional records inserted into the DimCustomer Table.

3 - Navigate back to your Dynamics 365 Accounts and refresh. Bingo!

3 new accounts were added to Dynamics 365 without touching Dynamics 365. Cool, right?

But hold on a second, in the table DimCustomer, the field CustomerAlternateKey has a strange number. They’re not the values we have inserted in the first place. Instead, we have the unique ID of the account generated in Dynamics 365. How is that possible?

If you look closely at the trigger OnDimCustomerInsert, you will notice that SELECT SCOPE_IDENTITY(); is the responsible for returning the account ID that will be used to update the table DimCustomer. That will be crucial to perform updates on existing accounts in Dynamics 365 when a record is changed in the table DimCustomer.

To prove our point, update the records created by executing in SSMS the following code:

UPDATE [AdventureWorksDW].[dbo].[DimCustomer] SET LastName = 'LTD' WHERE CustomerAlternateKey = 'account(9a1bc5cb-6ad7-e811-a96f-000d3a2cb90b)';

NOTE: REPLACE THE STRING account(9a1bc5cb-6ad7-e811-a96f-000d3a2cb90b) WITH THE VALUES GENERATED IN YOUR ENVIRONMENT.

After executing the updates for the 3 accounts, here is the result in Dynamics 365.

These are simple examples, but they do demonstrate how this works in practice. It is a scalable and powerful solution that allows you to seamlessly:

• Centralize everything that was previously connected to your on-prem Dynamics.
• Move to the online version of Dynamics and keep the existing integrations working normally.

And, not least important:

• There is no need to learn any new technology or programming language! It’s as easy as it gets.
• You can enhance the process by modifying the triggers.

Also, if you immediately scrolled down to find out which other Dynamics 365 solutions you can easily connect to using Connect Bridge, this is that part!

They are:

• Dynamics 365 Finance and Operations;
• Dynamics 365 Business Central;
• Dynamics 365 Customer Engagement.

Such a treat!

If you have any comments, or suggestions, write them in the space below and we’ll respond ASAP.