How to Export Data from Dynamics 365 Business Central

How to Export Data from Dynamics 365 Business Central

Dynamics 365 Business Central provides six primary ways to extract data: Open in Excel from list pages, Configuration Packages for structured table exports, reports saved to Excel or PDF, BACPAC database exports for full snapshots, the REST API v2.0 for programmatic access, and OData web services for page-based queries. The right method depends on whether you need a one-off spreadsheet, a bulk migration dataset, or a live integration feed.

This guide covers each extraction method with its real constraints, API rate limits, deprecation timelines, throughput benchmarks, and the trade-offs that matter when you're planning a migration or building an integration.

Deployment Type Matters: SaaS vs. On-Premises Method Availability

Before choosing an extraction method, confirm your deployment type. Not all methods are available in all configurations.

For Business Central on-premises, you can read directly from the environment database using standard SQL tooling — no API or BACPAC required. That option is unavailable in Business Central online (SaaS), which is why the API and BACPAC paths exist for cloud deployments.

Open in Excel: Quick Exports from List Pages

Open in Excel is Business Central's simplest export path — a one-click static snapshot of whatever list page you're viewing.

From any list page in Business Central, open the list you want to export (e.g., Customer List, Item List, General Ledger Entries), apply filters to limit the data, then select Share in the top-right corner and choose Send to Microsoft Excel. Business Central exports the visible rows and columns to an .xlsx file.

Hard limits on this approach:

• The export is read-only with no live connection back to Business Central. It captures data at the moment of export.
• Only visible columns are included — hidden or non-displayed fields are not exported.
• You cannot export related tables or sub-records (e.g., sales lines from the sales order list).
• Row limits follow Excel's own worksheet maximum: 1,048,576 rows per sheet.

When to use it: Ad-hoc analysis, quick data pulls for a stakeholder, or verifying data before a larger extraction. Not suitable for migrations or ongoing sync.

Configuration Packages: Table-Level Bulk Export

Configuration Packages (formerly known as RapidStart Services) is Business Central's built-in tool for exporting and importing structured table data in bulk. It is designed for master data and setup data — not high-volume transactional extraction.

How to create a Configuration Package export

1. Search for Configuration Packages in Business Central and open the page.
2. Click New and assign a meaningful code and name to the package.
3. Add the tables you need. Common table IDs: Table 18 (Customer), Table 23 (Vendor), Table 27 (Item), Table 36 (Sales Header), Table 38 (Purchase Header).
4. For each table, select which fields to include. To set a filter, navigate to the table line and choose Table → Fields, then use the Field Filter column.
5. Filter syntax follows standard Business Central filter notation: .. for ranges (e.g., 01/01/2024..31/12/2024), | for OR conditions, <> for exclusions.
6. Choose Export to Excel (.xlsx) or Export Package (.rapidstart binary format).

In the exported Excel file, each Business Central table appears as a separate worksheet.

Constraints, schema requirements, and edge cases

• Schema must match between environments. When importing a Configuration Package into a different company or environment, both databases must have the same table and field structure — identical primary keys, field IDs, and data types. Schema drift from extensions will cause import failures.
• System tables are blocked. You cannot import or export system tables through Configuration Packages. For example, system table 2000000006 (Object) cannot be included in a package.
• Posted ledger entries cannot be round-tripped. You can export from posted entry tables (e.g., Customer Ledger Entries, Item Ledger Entries), but you cannot import back into them through Configuration Packages. Re-creating posted entries requires journals or opening balance transactions.
• BLOB fields are unreliable. In some versions, only one row of BLOB data exports correctly. Test BLOB-type fields explicitly before relying on Configuration Package exports for any table that uses them.
• FlowFields are inconsistent. Calculated fields (FlowFields) such as customer balance or inventory on hand may export with stale or zero values depending on whether they were recalculated before export.

Warning: Configuration Packages are not designed for hundreds of thousands of records. If you need ledger entry volumes at that scale, use the REST API v2.0 with lastModifiedDateTime filtering, or a BACPAC export followed by SQL extraction.

BACPAC Database Export: Full Environment Snapshots

BACPAC export gives you a complete copy of your Business Central database — schema and all data — as a .bacpac file stored in Azure Blob Storage. This is the most complete single-operation extraction available for Business Central online.

The fastest (and least disruptive) way to get a historical data load from Business Central online is to export a BACPAC from the Business Central Admin Center and restore it to Azure SQL Database or SQL Server.

Hard limits on BACPAC exports

Once the BACPAC is generated and delivered to your Azure Blob Storage account, you restore it into Azure SQL Database or a local SQL Server instance and query it with standard SQL tools, SSMS, Azure Data Studio, or an ETL tool of your choice.

What BACPAC includes — and what it doesn't

A BACPAC export includes every table in the database: posted entries, dimension entries, change log entries, extension tables, and custom tables. It is a point-in-time snapshot, not a live feed.

Critical caveat: FlowFields show as zero or null in BACPAC exports. FlowFields (e.g., Customer."Balance (LCY)", Item."Inventory") are calculated at runtime and are not stored in the underlying SQL tables. After restoring a BACPAC to SQL Server, these columns will be empty. You must either join the underlying transaction tables to recalculate them, or retrieve those values via the API before taking the BACPAC snapshot.

Restoring a BACPAC to Azure SQL

-- After restoring the BACPAC, connect to the restored database
-- Business Central table names in SQL follow this pattern:
-- [CompanyName$TableName$GUID]
-- Example: [CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]
 
SELECT TOP 100 * 
FROM [dbo].[CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]

When to use it: Historical data loads for a data warehouse, full-environment audits, migration baselines, or compliance snapshots.

REST API v2.0: The Recommended Programmatic Interface

The Business Central REST API v2.0 is the platform's purpose-built, versioned interface for machine-to-machine data extraction. Its base URL follows this pattern:

GET https://api.businesscentral.dynamics.com/v2.0/{tenantId}/{environment}/api/v2.0/companies({companyId})/{entity}

The standard API covers customers, vendors, items, sales orders, purchase orders, purchase invoices, journal entries, general ledger entries, accounts, tax groups, payment terms, and dozens of other entities. For tables not covered by the standard API, you can expose them by creating custom API pages written in AL (the extension development language for Business Central) and deploying them as extensions.

Business Central delivers a comprehensive set of APIs out of the box. For example, you can perform POST, GET, PATCH, and DELETE operations on the Item table without writing any code.

Authentication: OAuth 2.0 with Service Principals

Every REST API call to Business Central online requires OAuth 2.0 Bearer token authentication. Anonymous access is not supported.

Setup steps for service principal authentication:

1. Register an application in Azure Active Directory (Entra ID). Note the Application (client) ID and Directory (tenant) ID.
2. Create a client secret (or certificate) for the app registration.
3. In Business Central, open Azure Active Directory Applications and register the app by its Client ID. Assign it a permission set (e.g., D365 FULL ACCESS or a custom permission set scoped to the tables you need).
4. Request a token from the Microsoft identity platform:

POST https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token
Content-Type: application/x-www-form-urlencoded
 
grant_type=client_credentials
&client_id={appId}
&client_secret={secret}
&scope=https://api.businesscentral.dynamics.com/.default

5. Use the returned access_token as a Bearer token in all subsequent API calls:

GET https://api.businesscentral.dynamics.com/v2.0/{tenantId}/{environment}/api/v2.0/companies
Authorization: Bearer {access_token}

Tokens expire after 3,600 seconds (1 hour). Your extraction code must handle token refresh.

API rate limits

Business Central enforces per-user (per-service-principal) rate limits on all OData v4 requests, which includes the REST API v2.0.

The 6,000-per-5-minute rolling window is not a fixed interval reset — it slides continuously. A burst of 6,000 requests in the first 30 seconds of a 5-minute window will trigger throttling for the remaining 4.5 minutes even if your average rate looks acceptable.

Realistic throughput under rate limits

At the maximum sustained rate of 6,000 requests per 5-minute window (1,200 requests/minute, 20 requests/second average), with a typical page size of 100–500 records per response:

In practice, most entity responses return 100–500 records per page for standard entities. Complex entities with many expanded navigations return fewer. Use $top and $select to maximize records per response and minimize total request count.

Throughput scaling with multiple service principals: Each service principal receives its own independent rate limit quota. Three service principals give you 18,000 requests per 5-minute window (effectively ~216 million records per hour at 500 records/page). Distribute extraction across service principals by entity type or by company when running multi-company extractions.

Error handling: 429 retry strategy

When you receive HTTP 429, Business Central returns a Retry-After header specifying the number of seconds to wait before retrying. Implement exponential backoff with jitter as a fallback when Retry-After is absent:

import time, random
 
def api_call_with_retry(url, headers, max_retries=5):
    for attempt in range(max_retries):
        response = requests.get(url, headers=headers)
        if response.status_code == 429:
            retry_after = int(response.headers.get('Retry-After', 2 ** attempt))
            time.sleep(retry_after + random.uniform(0, 1))
            continue
        response.raise_for_status()
        return response
    raise Exception(f"Max retries exceeded for {url}")

For production pipelines, implement a circuit breaker: if 429 responses exceed a threshold (e.g., 10 consecutive failures), pause the pipeline for a full 5-minute window before resuming.

How to handle delta loads with the API

The recommended approach for incremental extraction is timestamp-based filtering using the lastModifiedDateTime system field, which is exposed on all standard API entities:

GET /api/v2.0/companies({id})/customers
  ?$filter=lastModifiedDateTime gt 2026-09-20T00:00:00Z
  &$select=id,number,displayName,lastModifiedDateTime
Authorization: Bearer {token}
Data-Access-Intent: ReadOnly

The Data-Access-Intent: ReadOnly header routes the query to a read-scale replica, reducing load on the primary database and improving query performance for large datasets.

Delta links are deprecated. OData @odata.deltaLink functionality was removed in Business Central 2023 release wave 2 (version 23) and 2024 release wave 1 (version 24). Microsoft's replacement is webhook-based notification.

Webhook-based delta sync

To receive push notifications when records change, register a webhook subscription:

POST /api/v2.0/subscriptions
Content-Type: application/json
 
{
  "notificationUrl": "https://your-endpoint.example.com/bc-webhook",
  "resource": "/api/v2.0/companies({companyId})/customers",
  "clientState": "your-secret-validation-token",
  "expirationDateTime": "2026-12-31T00:00:00Z"
}

Business Central sends a POST to your notificationUrl when records in the subscribed resource change. Your endpoint must:

1. Validate the clientState value in incoming notifications.
2. Respond with HTTP 200 within 30 seconds (or Business Central retries with exponential backoff).
3. Fetch the changed record using the id provided in the notification payload.
4. Renew the subscription before expirationDateTime — subscriptions expire and must be refreshed.

Webhook subscriptions are not a complete replacement for full-load extractions. Use webhooks for ongoing delta sync after an initial full-load baseline is established via the API or BACPAC.

Pagination

Business Central REST API responses are paged. When a response contains more records than fit in a single page, the JSON body includes an @odata.nextLink URL. Your extraction code must follow this link until no nextLink is returned:

url = f"https://api.businesscentral.dynamics.com/v2.0/{tenant}/{env}/api/v2.0/companies({company_id})/customers"
all_records = []
 
while url:
    response = requests.get(url, headers=headers).json()
    all_records.extend(response.get('value', []))
    url = response.get('@odata.nextLink')  # None when last page reached

Failing to follow @odata.nextLink is one of the most common causes of incomplete data extraction.

Creating a custom API page in AL

For tables not covered by standard API v2.0, create a custom API page in AL:

page 50100 "My Custom API"
{
    PageType = API;
    APIPublisher = 'mycompany';
    APIGroup = 'custom';
    APIVersion = 'v1.0';
    EntitySetName = 'myCustomEntities';
    EntityName = 'myCustomEntity';
    SourceTable = "My Custom Table";
    DelayedInsert = true;
    ODataKeyFields = SystemId;
 
    layout
    {
        area(Content)
        {
            repeater(GroupName)
            {
                field(id; Rec.SystemId) { Caption = 'id'; }
                field(code; Rec."Code") { Caption = 'code'; }
                field(description; Rec."Description") { Caption = 'description'; }
                field(lastModifiedDateTime; Rec.SystemModifiedAt) { Caption = 'lastModifiedDateTime'; }
            }
        }
    }
}

After deploying the extension, the endpoint is available at:

GET /api/mycompany/custom/v1.0/companies({id})/myCustomEntities

OData Web Services: Page-Based Queries (Deprecated Path)

OData web services let you expose any Business Central page as an OData v4 endpoint by registering it on the Web Services page. This approach still functions today but is on an active deprecation track.

Deprecation timeline:

• Version 30 (2027 Wave 1): OData support for Microsoft-authored pages is removed. Custom pages exposed as OData continue to work, but Microsoft strongly recommends migrating to API pages or API queries.

The core architectural problem with page-based OData: exposing a UI page object directly as a REST endpoint means UI logic (field visibility, FlowFields, page triggers) runs on every API call. This makes page-based OData slower and less stable than the REST API v2.0, which is decoupled from the UI layer.

If you're building a new integration or extraction pipeline today, use the REST API v2.0, not page-based OData.

SOAP Web Services: End of Life October 2026

SOAP endpoints on Microsoft-authored UI pages are fully removed in Business Central version 29, arriving with 2026 release wave 2 in October 2026.

Deprecation timeline

SOAP on custom UI pages you authored yourself continues to work beyond version 29 (though the protocol is still deprecated and migration is strongly recommended). SOAP on codeunits also continues beyond version 29.

Critical: If you have SOAP-based integrations calling standard Business Central pages, they will break when you upgrade to version 29. Use the RT0053 telemetry signal in Application Insights to identify which SOAP endpoints are being called and by which integrations. Audit now — many teams have integrations built years ago by partners who are no longer available, or by ISV add-ons where the SOAP calls are not visible at the BC layer.

Azure Data Factory and Microsoft Fabric for Large-Scale Extraction

For enterprise data warehousing or data lake scenarios, Azure Data Factory (ADF) provides a managed OData connector that can hit Business Central REST API or OData endpoints with built-in retry, parallelism, and sink adapters.

Typical pipeline architecture

1. ADF OData connector → authenticates via service principal, calls Business Central REST API v2.0
2. Incremental refresh based on lastModifiedDateTime timestamps stored in a watermark table
3. Sink to Azure Data Lake Storage Gen2, Azure SQL Database, Azure Synapse Analytics, or Microsoft Fabric Lakehouse

ADF vs. Fabric Notebooks trade-offs

For complex extraction scenarios — particularly those involving aggressive rate limit management, multi-company parallel extraction, or custom error handling — Fabric Notebooks (Python with requests or httpx) provide more control than ADF pipelines and are more reliable when throttling behavior is unpredictable.

Critical implementation details for ADF

• Always use $select to limit fields returned — this reduces response payload size and improves per-request record density.
• Always use $filter with lastModifiedDateTime for incremental loads.
• Store the last-successful-run timestamp in an external watermark table (Azure SQL or Fabric Lakehouse metadata table) to enable safe restarts.
• Handle @odata.nextLink — ADF's OData connector does this automatically, but verify it is enabled in your linked service configuration.

Choosing the Right Extraction Method

Common Pitfalls When Exporting Business Central Data

Ranked by frequency and severity of impact:

1. Missing pagination (causes silent data loss). The most common and most damaging mistake. Business Central returns @odata.nextLink when more records exist. If your extraction code does not follow this link on every response, it silently truncates output. Affects every API-based extraction. Always validate total record counts against Business Central's UI list counts after initial extraction.

2. Ignoring FlowFields (causes incorrect reporting). FlowFields — calculated fields like customer balance, inventory on hand, or vendor outstanding amount — are not stored in the database. They are computed at query time. In BACPAC exports, these columns appear as zero or null in the restored SQL database. In Configuration Package exports, values may be stale. In API responses, FlowFields are returned with current values. If your downstream system relies on these values from a BACPAC, you must recalculate them by joining source tables (e.g., calculate customer balance by summing Customer Ledger Entries) or pull them separately via API.

3. Forgetting multi-company structures (causes incomplete extractions). Each Business Central environment contains one or more companies. Every REST API call is scoped to a single company by the companies({companyId}) segment in the URL. A five-company environment requires five separate extraction pipelines, each consuming its own rate limit quota. Retrieve the company list first: GET /api/v2.0/companies.

4. Running extractions during business hours (causes user-facing slowdowns). Heavy API extraction competes for resources with interactive users. Use Data-Access-Intent: ReadOnly to route queries to the read replica, and schedule large full-load pulls outside peak business hours. Monitor the Business Central Admin Center telemetry for signs of performance degradation.

5. Missing the SOAP deprecation deadline (causes integration outages). Many integrations were built years ago by partners or ISVs where the underlying transport is not visible at the Business Central configuration layer. The RT0053 signal in Application Insights telemetry reports every SOAP call with its source endpoint. Run this report now, not in September 2026.

6. Not handling token expiry (causes intermittent pipeline failures). OAuth 2.0 tokens expire after 3,600 seconds. Long-running extractions that do not refresh the token mid-run will receive HTTP 401 errors partway through. Implement proactive token refresh when the token is within 300 seconds of expiry.

Data Portability: What You Can and Cannot Get Out

The BACPAC export gives you the entire database, which is the most complete extraction available. You get every transaction, every posting, every dimension entry, every extension table. Limitations:

• BACPAC files are SQL Server–specific artifacts. To use the data in a non-Microsoft system, restore to SQL Server or Azure SQL and extract from there using SQL queries or ETL tooling.
• FlowField columns in the restored database will be zero or null (see pitfall #2 above).
• The 10-export-per-month limit restricts how frequently you can take full snapshots.

The REST API v2.0 covers the most commonly needed entities but not every internal table. Some tables — posted document attachments, change log entries, certain system tables, and tables introduced by third-party extensions — require custom API pages to expose. Creating a custom API page requires AL development skills and extension deployment access.

For Business Central online (SaaS), Microsoft manages the underlying database and direct SQL access is not available. The API and BACPAC paths are the only routes to the raw data.

Appendix: Quick Reference

Key API limits

• Rate limit: 6,000 requests per 5-minute sliding window per service principal
• Max page size: 20,000 entities per response
• Concurrent requests: 5 per user/service principal
• Request timeout: 10 minutes
• Token expiry: 3,600 seconds

Key deprecation dates

• Version 26 (2025 Wave 1): SOAP on Microsoft pages disabled by default
• Version 29 (October 2026): SOAP on Microsoft pages permanently removed
• Version 30 (2027 Wave 1): OData on Microsoft-authored pages removed

Throughput estimates (REST API v2.0, single service principal)

• At 500 records/page, sustained rate: ~36 million records/hour
• At 100 records/page, sustained rate: ~7.2 million records/hour
• Three service principals in parallel: multiply by 3

BACPAC limits

• 10 exports per environment per month
• 200 GB maximum file size
• Standard Azure storage only (not premium)
• Admins only (internal or delegated)