How to Export Data from Dynamics 365 Business Central: APIs, Custom APIs and bc2adls

Dynamics 365 Business Central (BC) runs your finance, sales and supply chain, but sooner or later you need that data somewhere else: in Power BI, a SQL database, a data warehouse or another system. How you export it depends mostly on how much data you have and which tables you need.

This guide compares the three ways to extract data from Business Central for reporting and integration:

1. Standard APIs: ready to use, limited to Microsoft's entities.
2. Custom APIs: any table or field, built in AL.
3. The bc2adls extension: scheduled bulk exports to Azure Data Lake Storage or Microsoft Fabric.

If you only need a one-off file, see exporting to Excel first.

Business Central data export methods compared

Method	Setup	Custom fields and tables	Large tables	Best for
Standard APIs	None	No	Slow	Small, standard datasets
Custom APIs	AL development	Yes	Slow	Targeted integrations
bc2adls	Extension + Azure or Fabric	Yes	Yes, incremental	Reporting, data warehouses

1. Standard APIs

Business Central ships with a set of standard REST APIs (API v2.0) for core entities such as customers, vendors, items, sales orders and general ledger entries. Power BI, Excel, Power Automate and most integration platforms can call them directly.

Advantages and limitations

• No development: the endpoints are built and maintained by Microsoft.
• Widely supported: most BI tools and integration platforms can read them.
• Limited coverage: only the entities Microsoft exposes. Custom fields, extension tables and many standard tables are not available.
• Slow on large tables: data is paged over HTTP, so pulling ledger tables with millions of rows takes a long time.
• Throttling: Business Central online limits request rates and concurrent requests (see Microsoft's operational limits documentation).

Use it for small to medium datasets, quick dashboard data pulls, and cases where you don't need custom fields or tables.

2. Custom APIs

When the standard APIs don't cover what you need, a developer can build custom API pages in AL (PageType = API). They are exposed as REST endpoints just like the standard ones, but on the tables, fields and filters you choose.

Advantages and limitations

• Any data: expose custom fields, extension tables and any standard table.
• More control: apply filters and logic inside Business Central so the consumer receives only what it needs.
• Needs a developer: requires AL development, deployment and testing.
• Same performance limits: it's still an API, so large tables are still slow and have to be pulled in pages.
• Maintenance: custom APIs must be kept up to date as Business Central and your extensions change.

Use it for integrations that need custom fields or tables, and for controlled, filtered access by a specific external system.

3. The bc2adls extension

bc2adls (Business Central to Azure Data Lake Storage) is an open-source extension that exports the tables and fields you select to Azure Data Lake Storage or a Microsoft Fabric lakehouse. After the first full export it only sends records that were created, changed or deleted since the last run, on a schedule you set with job queue entries. From the lake, Power BI, Fabric, Azure Synapse or any tool that reads Parquet or Delta tables can use the data.

Advantages and limitations

• Handles large tables: incremental exports avoid re-reading whole ledgers through an API.
• Any table or field: including custom fields and extension tables.
• A foundation for a data warehouse: model, transform and combine the data with other systems in the lake.
• Running costs: you pay for Azure storage or Fabric capacity.
• Setup effort: it needs an Entra app registration and Azure or Fabric configuration. Our bc2adls setup guide walks through every step.

Use it for large datasets, Power BI reporting at scale, and building a data warehouse or lakehouse on Business Central data.

Exporting Business Central data to SQL Server or a data warehouse

Business Central online doesn't give you access to its underlying SQL database, so you can't query it directly. To get the data into SQL Server, Azure SQL or a data warehouse you have two main routes:

• bc2adls, then load: export to a Fabric lakehouse with bc2adls. The lakehouse's SQL analytics endpoint lets you query the tables with T-SQL right away, or you can load them into a Fabric warehouse or your own SQL database.
• A pipeline over the APIs: use Azure Data Factory, Fabric Data Factory or another ETL tool to call the standard or custom APIs and write the results to SQL. This works well for smaller tables but inherits the API limits above.

Business Central on-premises runs on your own SQL Server, so you can read its database directly. Read from a replica or a reporting copy rather than the live database, and keep in mind that table and column names include company names and extension IDs.

Quick option: exporting to Excel

For a one-off export you don't need any of the above. On most list pages, Share → Open in Excel downloads the rows and columns in the current view, including your filters. To export whole tables, a configuration package can export the selected tables and fields to Excel. Neither is suitable for scheduled reporting, but both are fine for ad-hoc analysis.

Not sure which table holds the field you need? Use Page Inspection to find the table and field names behind any page.

Which method should you choose?

Standard APIs are the quickest start for small, standard datasets. Custom APIs add access to any field or table but need a developer and still struggle with large tables. bc2adls is the most scalable option for reporting and data warehousing, at the cost of more setup and Azure or Fabric running costs.

If the goal is Power BI, our guide on connecting Power BI to Business Central compares the options from the reporting side. At Polaris Insights we build these pipelines and the Power BI reports on top of them, whichever extraction method fits your data.