New Source: Microsoft Dynamics Business Central

[TamGB]

Tell us about the new connector you’d like to have

Ability to extract Microsoft Dynamics Business Central data. Unfortunately, unlike other Microsoft Dynamics product, its information is only available via REST API, with the associated MSSQL database being hidden.

Describe the context around this new connector

Microsoft Dynamics Business Central can contain a multitude of projects including but not limited to, ERP, a critical source to monitor and integrate in a fulfilment perspective.

Regarding the endpoints, I believe these should be user-defineable given the large number of potential endpoints in addition to the developer creating its own endpoints in ERP since it is the only official way to access the underlying information.

Describe the alternative you are considering or using

What are you considering doing if you don’t have this integration through Airbyte? Unfortunately there are three options:

• Developing the connector via REST API SDK (a strong possibility)
• Maintain our current Python-based requests implementation
• Use an external Vendor which already supports this option such as Fivetran

Are you willing to submit a PR?

Yes!

[natalyjazzviolin]

Hello @TamGB, thank you for opening this issue - this sounds like a feature that would benefit a lot of users!

[saxjonas]

Second this. A Business Central connector would be a very good addition as I see a lot of bigger ecommerce actors moving towards BC as their preferred ERP. The API is thoroughly documented (https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/api-reference/v2.0/endpoints-apis-for-dynamics) and is consistent across endpoints.

EDIT: I see that there's a singer tap someone created here: https://github.com/nocubicles/tap-businesscentral
One thing to note is that the above tap is using basic auth, which would need to be replaced with OAuth.
Is this something that could be appropriated to fit airbyte? I've never built a connector myself so far so can't really tell.

[KevinConseilxyz]

I would be interested with such connector!

[sookeke]

Hello - Is this connector available in Airbyte? I am running the latest version of airbyte but cannot see this connector in my connector list https://docs.airbyte.com/integrations/sources/microsoft-dynamics-customer-engagement

[marcosmarxm]

The documentation you linked @sookeke is to retrieve data fro Microsoft Dynamic but using the MSSQL backend.
https://docs.airbyte.com/integrations/sources/microsoft-dynamics-customer-engagement#sync-overview

[sookeke]

Adding this feature will be a huge win for Airbyte!

[KevinConseilxyz]

@sookeke As a temporary solution, you can build a custom connector in Airbyte Cloud to connect to BC. I did it and it is "manageable" :). I can help out punctually if needed

[xavier-lai]

Hi @kevinshareGmbH, I'm on my way to creating my custom connectors.

How did you handle the multiple companies in BC ? I see two options :

1. create a source connector for each company? This would lead to better separation, easier debugging, scheduling, and monitoring, but it would require creating multiple tables. Also, when I add a new company, I would have to duplicate a connector and adjust my DBT in the transformation stage.
2. Or, create a unique source connector where a parent stream fetches the list of companies and then runs child streams for each company, uploading data to the same table with an added field for the company? This would automate the process from EL to the Transform stage when a new company is created, but there would be no separation, which could lead to complex debugging.

Best,

[KevinConseilxyz]

@xavier-lai I did not have this use case as i only handle one company. However if I were you I would definitely create one connector per company

[M0M0K]

Hey @xavier-lai, have you been able to create your custom connector ? I also need to create one, but I have 0 experience with Airbyte. If you succeded, how long did it take ? And how often do you need to maintain it ? Thanks

[xavier-lai]

Hey @M0M0K , yes, it's pretty easy when you want to connect to a basic API like REST. Maintenance depends on how you have developed your connector; for example, when you have to change streams for a lot of connections, it will be faster if all connections depend on the same custom source connector. To do that with BC, use variables and inject them with the Jinja format. My input variables are:

required:
- client_id
- client_secret
- start_date
- api_group
- api_publisher
- company_name
- company_id
- environment_name

With this you can't set a connection by company and still be fast to maintain because all connections depends to the same source custom connector

[xavier-lai]

Hey @TamGB, I confirm that a such connector should be a massive helper. MoreOver, we shoud let a template of connector to attach custom APIs created within the ERP 🚀

[marcosmarxm]

For folks interesting how to build a MDBC connector using the connector builder @KevinConseilxyz created a tutorial explaining some options: #35726