How do I connect to BC365 custom APIs through Pipeline Copy data activity?

[useruserhi91]

I have a custom/"advanced" API in BC365, and I want to pull all data from this API into my Fabric Warehouse (or Lakehouse).

I want to use the ODATA connection in Copy data activity within pipelines for this.

I am presented with the above, and I need help on:
1) What to login as - anonymous or basic authentication. And if "basic", do I simply put in my Microsoft email and password? (I am global admin).

2) What do I put into the "URL" field?

The data I specifically want to pull are salesInvoiceLines from BC custom API. I need to pull all the salesInvoiceLines. I have tried so far to put this query in, but I get "invalid credentials": https://api.businesscentral.dynamics.com/v2.0/{{tenentid}}/Production/api/{{anonymized}}/SalesInvoices/v2.0/companies(guid)/salesInvoiceLines(guid).

If anyone has tried this before, I'd appreciate more specific instructions, as the ODATA connection is new for me in Fabric. I may be completely off in my query. Thanks!

[useruserhi91 · Accepted solution]

I have a custom/"advanced" API in BC365, and I want to pull all data from this API into my Fabric Warehouse (or Lakehouse).

I want to use the REST connection in Copy data activity within pipelines for this. For the "base URL" I provided the base URL (https://api.businesscentral.dynamics.com/v2.0/{{tenentid}}) and it seemed to work upon testing the connection:

I am lost on how to proceed from here with: What to provide for other parameters and how to get these parameters?

The data I specifically want to pull are salesInvoiceLines from BC365 custom API. I need to pull all the salesInvoiceLines. So something like this, I assume: https://api.businesscentral.dynamics.com/v2.0/{{tenentid}}/Production/api/{{anonymized}}/SalesInvoices/v2.0/companies(guid)/salesInvoiceLines(guid).

I'd appreciate specific instructions, thanks!

[v-yilong-msft · Accepted solution]

Hi @useruserhi91 ,

For your first question, I think for ODATA connections you can use either anonymous or basic authentication. Since you mentioned that you are a global administrator, it would be safer and more appropriate to use basic authentication.

You can refer to the official documentation below to get a more in-depth understanding of both approaches:

Set up your OData connection - Microsoft Fabric | Microsoft Learn

For your second question, I think the URL that needs to be entered should point to the ODATA endpoint of the Business Central instance. For extracting salesInvoiceLines, the URL format should be:

https://api.businesscentral.dynamics.com/v2.0/{tenantid}/Production/ODataV4/Company('{companyid}')/salesInvoiceLines

Replace {tenantid} with the actual tenant ID and {companyid} with the query company ID.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[useruserhi91 · Accepted solution]

I have a custom/"advanced" API in BC365, and I want to pull all data from this API into my Fabric Warehouse (or Lakehouse).

I want to use the REST connection in Copy data activity within pipelines for this. For the "base URL" I provided the base URL (https://api.businesscentral.dynamics.com/v2.0/{{tenentid}}) and it seemed to work upon testing the connection:

I am lost on how to proceed from here with: What to provide for other parameters and how to get these parameters?

The data I specifically want to pull are salesInvoiceLines from BC365 custom API. I need to pull all the salesInvoiceLines. So something like this, I assume: https://api.businesscentral.dynamics.com/v2.0/{{tenentid}}/Production/api/{{anonymized}}/SalesInvoices/v2.0/companies(guid)/salesInvoiceLines(guid).

I'd appreciate specific instructions, thanks!