You can use a connector of type Database to directly connect to an external database via ODBC. Usually, this external database is an on-premises database. And often, D365 FO is run in the cloud. In this case an Azure Service Bus is required to connect to the external database.
However, you can also run D365 FO on-premises. If you do so, you can directly connect to an external database. You can connect in two ways:
Only without an Azure Service Bus
If you run D365 FO on-premises, and you want to connect to an external database, no Azure Service Bus is required to connect. You can use this way of connecting both for import and export of data.
To be able to connect to the external database:
- On the D365 FO server, create a data source name (DSN) .
- In D365 FO, set up an Database connector and an ODBC document. Depending on the data integration direction, on the message, use the Database connector and ODBC document as source or target.
To set up the connection, use a connector of type Database. For the connector, several specific settings are required:
| Field group |
Field |
Description |
| Azure Service Bus |
Windows Azure Service Bus namespace |
Leave empty. |
| Service bus key |
Leave empty. |
| ODBC data source |
Data source name |
Enter the data source name (DSN) as created on the external server. |
| Operator name |
You can define the login ID to be used to connect to the data source name (DSN) as created on the external server. |
| Password |
You can define the password to be used to connect to the data source name (DSN) as created on the external server. |
Example:
Note:
- The operator and password are only applied if no Login ID and Password are defined for the DSN.
- If you do not define a login ID and password here, make sure the Login ID and password are defined on the DSN.
Without an Azure Service Bus and using Direct SQL
If you run D365 FO on-premises, you can directly connect to another on-premises SQL server database with direct SQL. So, no Azure Service Bus is required to connect. You can use this connection only to export data from a D365 FO (on-premises) database. Compared to the 'Only without an Azure Service Bus' connection, the direct SQL connection is faster, but has some limitations.
To be able to connect to the external database:
- On the external on-premises SQL server, create a linked server.
- In D365 FO, set up an Database connector and an ODBC document. On the message, use the Database connector and ODBC document as target.
Linked server
On the external on-premises SQL server, create a linked server . The linked server is used to link the external on-premises SQL database to the D365 FO database. Make sure, the linked server can read from the D365 FO database.
Note: If the external on-premises SQL database runs on the same server as the D365 FO database, no linked server is required.
Connector
To set up the connection, use a connector of type Database. For the connector, several specific settings are required:
| Field group |
Field |
Description |
| Azure Service Bus |
Windows Azure Service Bus namespace |
Leave empty. |
| Service bus key |
Leave empty. |
| ODBC data source |
Operator name |
You can define the login ID to be used to connect to the external database server. If not defined, the connection is automatically handled as trusted. |
| Password |
You can define the password to be used to connect to the external database server. If not defined, the connection is automatically handled as trusted. |
| Direct SQL |
Linked server instance |
Enter the name of the linked server instance that is defined on the external SQL server to connect to the D365 FO database. |
| Database server name |
Enter the SQL server instance name of the external on-premises SQL server where you created the linked server.
Instead of the Data source name, define the Database server name and Database name. You connect to the external database using the driver instead of the DSN. |
| Database name |
Enter the name of the applicable database on the defined SQL server instance. |
| ODBC driver |
If you use direct SQL, you connect using the driver. By default, the 'SQL Server Native Client 11.0' ODBC driver is used. Optionally, you can define another, ODBC driver. Example: 'ODBC Driver 13 for SQL Server'. |
Example:
Note: The blurred fields are optional.
Message header
To export data with direct SQL, some specific settings are required on the message header:
| Section |
Field |
Description |
| General |
Source connector |
Must be of type 'D365 FO'. |
| Source document |
Must be of type 'D365 FO'. |
| Target connector |
Must be of type 'Database'. |
| Target document |
Must be of type 'ODBC'.
Make sure, the target document only has one record. This is because the 'Insert into' command only supports one table at a time. |
| Custom |
Message type |
Select ‘SQL’. |
| Handler |
Select ‘BisMessageRunDirectSQL’. This handler class gets the data from the D365 FO database and directly enters it into the external on/premises SQL database. |
Example:
Message mapping
If you use direct SQL, the standard D365 FO logic cannot be applied. The field mapping is handled by the SQL statement which is executed on the external SQL server. Therefore, not all message mapping options are supported.
Supported message mapping options:
- Record action: Update or insert. The other record actions are not possible for direct SQL.
- Key field
- Constant values
- Value transformations
- Custom handler: Custom handlers are executed only once when the message starts. The result is used in the mapping.
- Dimension set
- Ledger
Unsupported message mapping options:
If you use direct SQL, these message mapping options are not supported:
- Record action: Except for 'Update or insert', no record actions are supported.
- Target mapping: On the Field mapping, in the Source record field, on the lookup, you can change the Direction to Target to create a target mapping. For direct SQL, this is not supported in the execution of the message.
- Default value
- Range transformations
- Type conversion
- Display method
- Edit method
- Company-specific field mapping
Run direct SQL message
Each time, the message is run, an SQL statement is created and run on the external SQL server. This SQL statement reads the data from D365 FO and writes it to the external on-premises database.
If, on the message field mapping, you have:
- Defined key fields, two SQL statements are run: One to update existing records and one to insert records.
- Not defined key fields, only the SQL statement to insert records is run.
Example of direct SQL message
As an example, three field values are exported from the D365 FO CustTable to the customers table in the external SQL database. The applicable message has this record and field mapping:
In the example, a key field is used: AccountNum. As a result, when the message is run, two SQL statements are created and run on the external SQL database:
- SQL statement to update existing records:
UPDATE TrgCustomer
SET TrgCustomer.customergroup = custtable.CustGroup ,
TrgCustomer.currency = custtable.Currency
FROM [MyDatabase].[dbo].[TrgCustomer] TrgCustomer
join [LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable] on
custtable.DataAreaId = 'usmf' and
custtable.Partition = 5637144576
AND TrgCustomer.Id = custtable.AccountNum
- SQL statement to insert new records:
INSERT INTO [MyDatabase].[dbo].[TrgCustomer] (Id ,customergroup ,currency )
Select custtable.AccountNum ,custtable.CustGroup ,custtable.Currency
FROM [LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable]
Where custtable.DataAreaId = 'usmf' and
custtable.Partition = 5637144576 AND
NOT EXISTS ( select * from [MyDatabase].[dbo].[TrgCustomer]
where TrgCustomer.Id = custtable.AccountNum )
Explanation of several parts in these statements:
| Statement line |
Element |
Description |
| [MyDatabase].[dbo].[TrgCustomer] |
[MyDatabase] |
The database as defined in the Database name field on the target connector. |
| [dbo] |
The owner schema as defined in the SQL owner schema field on the target document record details. |
| [TrgCustomer] |
The applicable record of the target document. |
| [LinkedServerToD365Fo].[AxDB].[dbo].[CUSTTABLE] [custtable] |
[LinkedServerToD365Fo] |
The linked server the linked server instance of the external SQL server, as defined in the Linked server instance field on the target connector. |
| [AxDB] |
The standard database name for the D365 FO database. |
| [dbo] |
The standard SQL owner schema of D365 FO. |
| [CUSTTABLE] |
The applicable record of the source document. |
