Microsoft Dynamics and SSIS: A 2023 Roundup

By /

I have spent time with various parts of the Microsoft Dynamics stack over the years, from Great Plains and NAV through to Business Central and SSIS. Here are some notes on what changed across these products in 2023.

Dynamics GP 18.5

The latest GP release drops support for SQL Server 2014 and adds support for Windows 11, Windows Server 2022 and SQL Server 2022. On the usability side, you can now print or email cash receipt documents directly, and the account category lookups have been improved. Not headline features, but they save time day to day.

Business Central: 2023 Release Wave 2

The main addition to Business Central this year is Microsoft Copilot, which handles things like auto-completing routine data entry and suggesting next actions. Beyond that, the Wave 2 release brings improvements to reporting and general usability.

NAV 2016/2018: Integration Events

One of the more useful features introduced in NAV 2016 was Events in C/AL. This lets you hook into standard application processes without modifying the base code directly. You can define your own custom events or subscribe to the predefined ones that ship with NAV. It makes customisations much cleaner to maintain across upgrades.

SSIS with the Business Central/NAV REST API

If you need to move data between Business Central (or NAV) and other systems, SSIS with the REST API v2 is a solid option. The setup involves:

  • An OData Connection Manager pointed at the Dynamics server
  • Authentication via Basic or OAuth2 (OAuth2 is the better choice for production)
  • The OData Source component for reading and writing data without additional coding

5. Example: SQL procedure for data sync

Here is a stored procedure for syncing customer records between an external CRM and a GP database:

-- SQL Stored Procedure for Data Sync
CREATE PROCEDURE UpdateCustomerData AS
BEGIN
    -- Pull updated customer data from CRM via linked server
    SELECT CustomerID, Name, Address, TotalPurchases
    INTO #UpdatedCustomers
    FROM OPENQUERY([CRM_DB], 'SELECT ID, Name, Address, TotalPurchases FROM Customers')

    -- Update GP customer table
    UPDATE GP
    SET GP.Name = CRM.Name,
        GP.Address = CRM.Address,
        GP.TotalPurchases = CRM.TotalPurchases
    FROM DynamicsGP.dbo.Customers GP
    INNER JOIN #UpdatedCustomers CRM ON GP.CustomerID = CRM.CustomerID
END

Nothing fancy, but it keeps the two systems in sync without manual re-keying.

For a hands-on AL example, see my post on building a custom inventory page in Business Central. If you hit issues publishing, I’ve documented a fix for error code 85132273.

Scroll to Top