Microsoft Dynamics 365 Business Central (JDBC)

Documentation

Getting Started

Global Functions

Lucanet Lume

Administration

Data Hub

Integrations

Microsoft Dynamics 365 Business Central Beta

Microsoft Dynamics 365 Business Central (JDBC)

Microsoft Dynamics 365 Business Central (OData)

SAP S/4HANA (RFC)

Managing Connections

Managing Source Datasets

Importing Data

Consolidation & Financial Planning

Lease Accounting

Extended Planning & Analysis (xP&A)

ESG Reporting

Disclosure Management

XBRL Tagger

Tax Compliance & Reporting

Banking & Cash Management

Data Collection

Glossary

The Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub extracts data directly from the database of an on-premises Microsoft Dynamics 365 Business Central installation, including former Microsoft Dynamics NAV (Navision) installations, through JDBC and writes it into the Lucanet Data Lake as raw Parquet datasets. From the Data Lake, the data is available for further processing on the Lucanet CFO Solution Platform.

The Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub also extracts data from other Microsoft SQL Server, Oracle Database, or PostgreSQL databases. When you create the connection, you select the database driver: Microsoft SQL Server, Oracle Database, or PostgreSQL. All database drivers have the same options and differ only in the format of the JDBC connection URL.

This page describes the technical details and the prerequisites of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub, every step from creating the connection to starting the import, the connection settings, every option of the source dataset configuration, example configurations, a configuration checklist, and the release notes.

This document describes version 1.0 of the Microsoft Dynamics 365 Business Central (JDBC) integration, which applies to all three database drivers. In the connection form, the version is displayed in the Source system area, for example Version: 1.0.0. Bugfix versions such as 1.0.1 are applied automatically and do not change the options described on this page. The changes of each version are listed in the release notes.

The Microsoft Dynamics 365 Business Central (JDBC) integration uses the script #3724-GenericDataUploadViaJDBC and is executed by the Lucanet.Script Execution Application (SEA). It reads the data from the Microsoft SQL Server or Azure SQL database of Business Central, or from another Microsoft SQL Server, Oracle Database, or PostgreSQL database, with an SQL query or a stored procedure. Lucanet.Financial Client is not involved.

The Microsoft Dynamics 365 Business Central (JDBC) integration writes the data in raw form. It does not map or transform any values into a target schema: the columns of the query result are stored as they are, with automatic data-type detection. You can join, filter, or map the data later in the Data Lake.

The Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub requires the following prerequisites. They concern the environment and are not options of the integration:

A license for the

Microsoft Dynamics 365 Business Central (JDBC)

integration. Without a license,

Microsoft Dynamics 365 Business Central (JDBC)

is not displayed in the

Source system

drop-down list.

A running SEA instance that executes the

Microsoft Dynamics 365 Business Central (JDBC)

integration

Network connectivity from the SEA instance to the database server and its port, for example through a VPN. The default port is 1433 for Microsoft SQL Server, 1521 for Oracle Database, and 5432 for PostgreSQL.

A database login with a user name and a password. The login requires read permission on every table or view used in the query, or the permission to execute the stored procedure.

Confirmation from the customer of which tables contain the required data. In the Business Central database, the table names contain the name of the company, for example

CRONUS$G_L Entry

, and can have an additional suffix.

To import data with the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub, proceed as follows:

Create a connection with Microsoft Dynamics 365 Business Central (JDBC) as the source system, select the database driver, and enter the settings for accessing the database. For more information, see Creating a Connection for the Microsoft Dynamics 365 Business Central (JDBC) Integration.

Create a source dataset that uses the connection and specify the data to extract from the source system in its Configuration area. A connection can have one or more source datasets. For more information, see Creating a Source Dataset for the Microsoft Dynamics 365 Business Central (JDBC) Integration.

Start an import of the source dataset. Before the first import, check the points of the configuration checklist for the Microsoft Dynamics 365 Business Central (JDBC) integration. For more information, see Starting an Import for the Microsoft Dynamics 365 Business Central (JDBC) Integration.

A connection of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub specifies the settings that Data Hub uses to access the database of Business Central.

To create a connection for the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub:

In the main menu, select Data Hub. The Integrations page is displayed as follows, for example:

Shows the 'Integrations' page in Data Hub with the 'Create new connection' button and a table with the columns 'Connection name, Number of associated data sources', 'Source system', 'Status', 'Created by', and 'Import'. The table lists connections with the source systems 'Microsoft Dynamics 365 Business Cen', truncated in the column, and 'SAP S/4HANA (RFC)', all with the status 'Active'. Highlighted in red is 'Data Hub' in the main menu. — Connections and their source datasets on the 'Integrations' page

In the top-right corner, click Create new connection. The connection form is displayed as follows, for example:

Shows the connection form in Data Hub with the empty 'Enter connection name' field and the message 'This field is required.', the 'Test connection', 'Cancel', and 'Create' buttons in the top-right corner, and the open 'Source system' drop-down list with the options 'Microsoft Dynamics 365 Business Central (JDBC)', 'Microsoft Dynamics 365 Business Central (OData)', 'Microsoft Dynamics 365 Business Central Beta', and 'SAP S/4HANA (RFC)'. — Connection form with the source-system selection

In the Source system drop-down list, select Microsoft Dynamics 365 Business Central (JDBC). The version of the integration is displayed in the top-right corner of the Source system area.

At the top of the connection form, enter a name for the connection in the Enter connection name field. The name identifies the connection on the Integrations page.

In the Configuration area, select the SEA instance that runs the connection. For more information, see Lucanet.Script Execution Application.

In the Connection area of a Microsoft Dynamics 365 Business Central (JDBC) connection, enter the following information:

Option	Required	Description	Example
Database driver	Yes	Database that the connection accesses: Microsoft SQL Server, Oracle Database, or PostgreSQL. Data Hub uses the matching JDBC driver. For a Business Central installation, select Microsoft SQL Server, also for an Azure SQL database, because Business Central runs on Microsoft SQL Server or Azure SQL only.	Microsoft SQL Server
URL	Yes	JDBC connection URL of the database. The URL contains the host, the port, and the name of the database, and its beginning depends on the database driver.	`jdbc:sqlserver://dbserver.example.com:1433;databaseName=BC`

The following table shows how the URL begins for each database driver of the Microsoft Dynamics 365 Business Central (JDBC) integration:

Database driver	URL must start with	Example
Microsoft SQL Server	`jdbc:sqlserver://`	`jdbc:sqlserver://hostname:1433;databaseName=myDatabase`
Oracle Database	`jdbc:oracle:thin:`	`jdbc:oracle:thin:@//hostname:1521/servicename`
PostgreSQL	`jdbc:postgresql:`	`jdbc:postgresql://hostname:5432/mydatabase`

In the Authentication area of a Microsoft Dynamics 365 Business Central (JDBC) connection, enter the following information:

Option	Required	Description	Example
User	Yes	User name of the database login	`lucanet_reader`
Password	Yes	Password of the database login	(secret)

Optionally, enter a description of the connection in the Description field at the bottom of the connection form.

Optionally, click Test connection in the top-right corner to check whether Data Hub can reach the database of Business Central with the settings you have entered. Data Hub tests the settings without saving them. For more information, see Testing a Connection.

Click Create. The connection is created and displayed on the Integrations page.

A source dataset of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub specifies which data to import from the database of Business Central. Each source dataset belongs to a connection, and a connection can have one or more source datasets.

You create a source dataset for the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub in three parts, in this order: first the general options of the source dataset, then the tabs of the Configuration area, and finally saving the source dataset.

To specify the general options of a source dataset for the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub:

On the Integrations page, click the icon in the row of the Microsoft Dynamics 365 Business Central (JDBC) connection and select Create new dataset.

In the Name field, enter a name for the source dataset. The name identifies the source dataset on the Integrations page.

In the Connection drop-down list, check the connection. The connection from which you create the source dataset is preselected. If necessary, you can select a different connection. The Configuration area then displays the options of the selected source system. After the source dataset is created, the connection can no longer be changed.

In the Namespace field, enter a technical identifier for the source dataset. The namespace is used to reference the data tables of the source dataset in SQL queries against the Data Lake, for example namespace.table_name. The namespace can have up to 255 characters and must be SQL-safe, with no spaces or unsupported special characters.

Optionally, define variables in the Variables area. Variables are placeholders in the configuration whose values are selected every time an import is started. For more information, see Variables.

The Variables area is displayed as follows, for example:

Shows the 'Variables' area of a source dataset in Data Hub with the columns 'Variable name', 'Display name', 'Data type', and 'Selection type' and the variables 'PERIOD' ('Period', 'String', 'Single value', 3 values) and 'YEAR' ('Fiscal year', 'String', 'Single value'), and the 'Add variable' button. Highlighted in red is 'Configure values' in the row of the variable 'YEAR'. — Variables area of a source dataset

In the Configuration area of a Microsoft Dynamics 365 Business Central (JDBC) source dataset, the General configuration tab contains the option that applies to both extraction methods.

The following table describes the option on the General configuration tab of the Microsoft Dynamics 365 Business Central (JDBC) integration:

Option	Required	Description	Example
Name of uploaded table	Yes	Name under which the extracted data is stored as a dataset in the Data Lake. The name is used in lower case. Required for both extraction methods	`gl_entry_2026`

The Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub extracts the data in one of two ways:

Prepared statement

: Executes an SQL query against the database. Use this method to read tables or views directly, for example with a filter on the posting date.

Stored procedure

: Calls a stored procedure in the database and loads the result that it returns. Use this method when the customer provides a stored procedure that returns the data.

Each extraction method is configured on its own tab of the Configuration area of a source dataset.

For each source dataset, fill in exactly one of the fields Input query and Stored procedure name. The integration checks the configuration when the import is executed: if you fill in both fields or neither field, the import fails with an error.

The following rules apply to both extraction methods of the Microsoft Dynamics 365 Business Central (JDBC) integration:

The query or the stored procedure must return a result. Each column of the result is stored as a column of the dataset in the Data Lake.

Every column of the result requires a unique name.

The columns are stored with the names that the database returns. Oracle Database returns names that are not enclosed in quotation marks in upper case (for example

ENTRY_NO

), PostgreSQL in lower case. Use aliases (

AS

) to control the column names.

The integration executes the query or the stored procedure twice per import: first to determine the data types of the columns, then to read the data. A stored procedure must therefore return the same result when it is called twice and must not change any data.

If you want the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub to read the data with an SQL query, use the Prepared statement tab of the source dataset. As soon as the Input query field is filled in, the Prepared statement tab is used for the extraction.

The following table describes the options on the Prepared statement tab of the Microsoft Dynamics 365 Business Central (JDBC) integration:

Option	Required	Description	Example
Input query	Yes	SQL query that is executed against the database, in the SQL syntax of the database. Can contain `?` placeholders, whose values you enter in the prepared statement parameter table.	`SELECT [Entry No_], [G_L Account No_], [Posting Date], [Amount] FROM [dbo].[CRONUS$G_L Entry] WHERE [Posting Date] BETWEEN ? AND ?`
prepared statement parameter table	No	Values for the `?` placeholders of the query. Each value is entered in its own row with the columns DataType and Value. The table requires exactly one row per placeholder. For more information, see Parameter Tables.	`Date`, `2026-01-01`

The following example shows a source dataset configuration of the Microsoft Dynamics 365 Business Central (JDBC) integration that reads the G/L entries of the company CRONUS for one year from a Microsoft SQL Server database:

Option	Value
Name of uploaded table	`gl_entry_2026`
Input query	`SELECT [Entry No_], [G_L Account No_], [Posting Date], [Amount] FROM [dbo].[CRONUS$G_L Entry] WHERE [Posting Date] BETWEEN ? AND ?`
prepared statement parameter table	`Date`, `2026-01-01` `Date`, `2026-12-31`

The example is a pattern: the table and column names depend on the Business Central database. In the database, the table names contain the name of the company and can have an additional suffix.

If you want the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub to load the data that a stored procedure returns, use the Stored procedure tab of the source dataset. As soon as the Stored procedure name field is filled in, the Stored procedure tab is used for the extraction.

The following table describes the options on the Stored procedure tab of the Microsoft Dynamics 365 Business Central (JDBC) integration:

Option	Required	Description	Example
Stored procedure name	Yes	Name of the stored procedure to call, optionally with the schema. The stored procedure must return a result. Output parameters are not supported. On PostgreSQL, enter a function that returns a table (`RETURNS TABLE` or `RETURNS SETOF`); procedures created with `CREATE PROCEDURE` are not supported. On Oracle Database, use the Prepared statement tab instead, for example with a table function. Note: On Microsoft SQL Server, use `SET NOCOUNT ON` in the stored procedure.	`dbo.LN_GetGLEntries`
Stored procedure parameter table	No	Input parameters that are passed to the stored procedure. Each parameter is entered in its own row with the columns DataType and Value. The integration passes one parameter per row. For more information, see Parameter Tables.	`Date`, `2026-01-01`

The following example shows a source dataset configuration of the Microsoft Dynamics 365 Business Central (JDBC) integration that calls a stored procedure with a start date and an end date:

Option	Value
Name of uploaded table	`gl_entry_sp_2026`
Stored procedure name	`dbo.LN_GetGLEntries`
Stored procedure parameter table	`Date`, `2026-01-01` `Date`, `2026-12-31`

The stored procedure in the example is a pattern and must exist in the database.

The parameter tables on the Prepared statement and Stored procedure tabs of the Microsoft Dynamics 365 Business Central (JDBC) integration contain the values that are passed to the query or to the stored procedure. Only the parameter table of the tab that is used for the extraction is read.

The parameter tables have the columns DataType and Value. They have no column for a parameter name, because each value is assigned to a parameter by its position. The integration passes the values as follows:

The first row fills the first parameter, the second row the second parameter, and so on.

On the

Prepared statement

tab, the number of rows must match the number of

?

placeholders in the

Input query

. Otherwise, the import fails with an error.

The value is converted into the data type selected in the

DataType

column.

The following table describes the data types in the DataType column of the Microsoft Dynamics 365 Business Central (JDBC) integration and the format of their values:

DataType	Format of the value
String	Text, passed as it is
Char	Single character. Only the first character of the value is used.
Byte, Short, Integer, Long	Whole number
Float, Double, BigDecimal	Number with a dot as the decimal separator, for example `1234.56`
Boolean	`true` or `false`. Every value other than `true` is passed as `false`.
Date	`yyyy-MM-dd`, for example `2026-01-31`
Time	`HH:mm:ss`, for example `23:59:59`
Datetime	`yyyy-MM-dd HH:mm:ss`, `yyyy-MM-ddTHH:mm:ss`, or `yyyy-MM-dd`
NULL	Passes an empty value (SQL NULL). The Value column is ignored.

The Microsoft Dynamics 365 Business Central (JDBC) integration detects the data types of the columns from the result of the query or the stored procedure. The following table describes how the database data types are stored in the Data Lake:

Data type in the database	Data type in the Data Lake
BIT, BOOLEAN	Boolean
TINYINT, SMALLINT, INTEGER, BIGINT	Integer of the matching size
REAL, FLOAT, DOUBLE	Floating-point number
DECIMAL, NUMERIC	Decimal number with up to 18 digits and the scale of the column, up to 18 decimal places. If the database reports no scale between 0 and 18, the integration uses 2 decimal places. Additional decimal places are rounded. Note: A PostgreSQL `numeric` column without a defined scale is stored without decimal places.
MONEY, SMALLMONEY (Microsoft SQL Server)	Decimal number with 4 decimal places
`money` (PostgreSQL)	Floating-point number
DATE	Date
TIME	Time
DATETIME, DATETIME2, SMALLDATETIME, TIMESTAMP, DATE (Oracle Database)	Date and time
All other data types, for example VARCHAR, NVARCHAR, and binary data, and also Oracle Database `BINARY_FLOAT`, `BINARY_DOUBLE`, and `JSON`, and PostgreSQL `uuid`, `json`, `jsonb`, and arrays	Text

If the query returns calculated values or columns without a fixed scale, for example SUM(amount), convert them to a decimal type with a fixed scale in the query, for example CAST(SUM(amount) AS NUMERIC(18,2)). Otherwise, decimal places can be lost.

Time zone information is not stored. PostgreSQL timestamptz values are converted to the time zone of the SEA instance. Oracle Database TIMESTAMP WITH TIME ZONE and Microsoft SQL Server DATETIMEOFFSET values are stored as text.

Empty values (NULL) of the database are not stored as empty values in the Data Lake. The Microsoft Dynamics 365 Business Central (JDBC) integration replaces them as follows:

Numbers:

0

Boolean values:

false

Dates:

1900-01-01

, times:

00:00:00

, dates with time:

1900-01-01T00:00:00

Text: an empty text

To save the Microsoft Dynamics 365 Business Central (JDBC) source dataset, click Create. The source dataset is created and displayed under the selected connection on the Integrations page.

After the first import, a source dataset can no longer be edited. Before the first import, all options except the connection can still be changed.

An import of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub extracts the data defined in a source dataset from the database of Business Central and writes it into the Data Lake. The Microsoft Dynamics 365 Business Central (JDBC) integration checks the configuration of the source dataset when the import is executed.

How an import of the Microsoft Dynamics 365 Business Central (JDBC) integration changes the data in the Data Lake depends on whether the source dataset uses variables. For more information, see Starting an Import in Data Hub.

To start an import of a Microsoft Dynamics 365 Business Central (JDBC) source dataset in Data Hub:

On the Integrations page, click the icon in the row of the source dataset you want to import.

Select Start import. If the source dataset uses no variables, Data Hub executes the import immediately.

If the source dataset uses variables, the Start Import Execution dialog is displayed as follows, for example:

Shows the 'Start Import Execution' dialog in Data Hub for the dataset 'dataset 2025-2026' with the text 'Set the import variables, then run the extraction.', the variable 'Period' set to 'January', and the open drop-down list of the variable 'Fiscal year' with the values '2025' and '2026'. — 'Start Import Execution' dialog of a source dataset with two variables

In the Start Import Execution dialog, select a value for every variable of the source dataset, then click Execute.

Data Hub imports the data and records the execution in the execution history of the source dataset. For more information on the execution history, see Importing Data.

After the first import, a source dataset of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub can no longer be edited. Before the first import, check the following points:

The connection uses

Microsoft Dynamics 365 Business Central (JDBC)

as the source system, the database is selected in

Database driver

(

Microsoft SQL Server

for Business Central), and the

URL

starts as required for it:

jdbc:sqlserver://

for

Microsoft SQL Server

jdbc:oracle:thin:

for

Oracle Database

, or

jdbc:postgresql:

for

PostgreSQL

User

and

Password

in the

Authentication

area are filled in, and the login can read the required tables.

The source dataset uses this connection.

On the

General configuration

tab, the

Name of uploaded table

is filled in.

Exactly one of the two extraction tabs is used:

Prepared statement

Input query

, plus one row in the

prepared statement parameter table

per

?

placeholder

Stored procedure

Stored procedure name

, plus the

Stored procedure parameter table

if the stored procedure has parameters

Every column of the result has a unique name.

The release notes of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub list the changes of each version with new or changed options, newest first. Bugfix versions are not listed.

Version 1.0 is the first documented version of the Microsoft Dynamics 365 Business Central (JDBC) integration in Data Hub and applies to the database drivers Microsoft SQL Server, Oracle Database, and PostgreSQL. It is based on version 1.0 of the script #3724-GenericDataUploadViaJDBC.

This content was generated using AI and reviewed by Lucanet subject matter experts before publication.

Page IDKB5467497

Last updated on Nov 13, 2026

Overview

Technical Details of the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Prerequisites for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Importing Data with the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Creating a Connection for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Creating a Source Dataset for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Configuring the 'General configuration' Tab

Configuring the Extraction Method

Configuring the 'Prepared statement' Tab

Configuring the 'Stored procedure' Tab

Parameter Tables

Data Types and Values in the Data Lake

Saving the Source Dataset

Starting an Import for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Configuration Checklist for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Release Notes for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Version 1.0

Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Overview

Technical Details of the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Prerequisites for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Importing Data with the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Creating a Connection for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Creating a Source Dataset for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Configuring the 'General configuration' Tab

Configuring the Extraction Method

Configuring the 'Prepared statement' Tab

Example Configuration for the 'Prepared statement' Tab

Configuring the 'Stored procedure' Tab

Example Configuration for the 'Stored procedure' Tab

Parameter Tables

Data Types and Values in the Data Lake

Saving the Source Dataset

Starting an Import for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Configuration Checklist for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Release Notes for the Microsoft Dynamics 365 Business Central (JDBC) Integration in Data Hub

Version 1.0

Table of Contents

Company

Legal Notice

Table of Contents

Related Articles

Company

Legal Notice