Complex Integration Requirement

(0) Share

Report

Posted on by babalalex

411

Hello everyone,

I have a new complex integration requirement as being a new bee to the integration requirement need some guidance or clue to proceed ahead with the integration process.

Here is the current working process.

1.JBOSS messaging platform - MES sends data into the on premises SQL DB.

2.Event Sheet Servers access this SQL DB and assign appropriate statuses line by line.

3.User sits with the event sheet servers and updates the statuses as

Statuses 1-2-3 indicates processing, 4 - Failed & 5 - completed

4.Completed lines are moved to SAP

Client want to replace SAP with the MIcrosoft Dynamics 365 FO So I am thinking of using SSIS. As I am very new to the integration part, I am not very sure how can I move ahead with it and what other credentials, are required for the integration part.

Can anyone guide me for the same ?

Thanks in advance.

Categories:

Data Integration

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Pedro Tornich 955 on at

Like (0)

Report

RE: Complex Integration Requirement

Hi babalalex,

You can use .Net code instead of a logic app, if that suits your needs better.

It will achieve the same goal, I just see logic apps as a simpler option.

You mentioned that at least one of your integrations must be real-time, so for this integration you cannot consider the file integration option, because Data Management file importation runs in batch, and it sometimes can delay almost 1min to start.

In my opinion you should go for a hybrid solution, using the batch data API (file integration) for the asynchronous and the OData API (web services) for the synchronous one.

Both, Batch API and OData API, can be accessed using .Net and Logic Apps, choose which one serves you better.

Was this reply helpful? Yes No
babalalex 411 on at

Like (0)

Report

RE: Complex Integration Requirement

@Nikolaos I am not sure about this, need to explore more.

@Martin Thanks for your input on SSIS, So I am using SSIS then to push data into F & O I have to do one more step.

As there are 10 operations in my process, 9 operations can be operated on asynchronous mode but last 10th operation has to be integrated with synchronous mode to ensure no time delay of more than 4-5 seconds.

So what do you suggest in this scenario instead of SSIS ?

Was this reply helpful? Yes No
Martin Dráb 236,184 Most Valuable Professional on at

Like (0)

Report

RE: Complex Integration Requirement

Data management in Finance and Operations is using SSIS under the hood.

You surely can use SSIS by yourself, but since you can't write into F&O database, you could merely use SSIS for transformation and then you would have to use one of the integration options mentioned above to push the data into F&O (most likely files).

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Complex Integration Requirement

I'm not very familiar with all capabilities of SSIS. But you need to know that you can't access the D365FO (cloud) database directly. Instead you need to talk to the application web services. Does this help you determine whether SSIS is feasible?

Was this reply helpful? Yes No
babalalex 411 on at

Like (0)

Report

RE: Complex Integration Requirement

Thanks Pedro for suggesting different ways.

As Client is having on premises SQL, I doubt that they will be ready to take their SQL to Azure SQL.

can not we use .Net application instead of Logic Apps from the following link ?

[View:https://bluefort.com.mt/news/dynamics-365-for-finance-and-operations-integration-with-on-premises-sql-server-using-odata:750:50]

Was this reply helpful? Yes No
babalalex 411 on at

Like (0)

Report

RE: Complex Integration Requirement

Thanks Nikolaos for prompt reply.

I am thinking of using SSIS for the integration purpose, would that be good alternative to think about ?

Was this reply helpful? Yes No
Suggested answer

Pedro Tornich 955 on at

Like (0)

Report

RE: Complex Integration Requirement

Hi babalalex,

When it comes to integration D365FO offers 3 main options:

File integration:
You can use data projects to import files with predefined format.

Database integration:
Data projects can also import data directly from an external database but currently only Azure-SQL is supported.

Webservice integration:
Microsoft designed D365FO to use OData web services. Almost all out-of-the-box entities are also an OData web service and to add new web services all you have to do is create a new Data Entity.

In my opinion if you could talk your client into moving this data from a SQL Server to an Azure SQL, the database integration would be the best option for you. It would be clean and easy to maintain.

Otherwise, you will have to export the data from the on-premises SQL to import it in D365FO. What could be easily done in CSV format, but involves manual action.

If you want an automated solution but your client doesn't want to go to Azure-SQL, you could try to use a Logic App to fetch the data from the on-premises SQL server and insert it in D365FO via OData web services (which is natively supported by logic apps).

Note: To access your on-premisses SQL data within the logic app you will need to install the On-premises Data Gateway (check this link for more info: https://www.serverless360.com/blog/access-on-premise-sql-server-data-from-azure-logic-apps-via-on-premises-data-gateway).

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: Complex Integration Requirement

Here are the possible integration approaches for D365FO: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/integration-overview

To me it seems you should go with the batch data API approach.

Was this reply helpful? Yes No