Help Manual - NAV Source Component (OData or REST API)

Documentation on the SSIS Integration Toolkit for Microsoft Dynamics 365

Use of NAV Source Component

The NAV Source Component, while using the OData endpoint or the REST API, would have the following settings that could be used to read the data from the NAV instance connected to. In the following two pages, you can configure how you want to read data from Microsoft Dynamics 365 BC/NAV:

  • General
  • Columns

General Page

The General page of the NAV Source Component allows you to specify the general settings of the component.

NAV OData Source General.png

NAV Connection Manager

The NAV source component requires a NAV connection in order to connect with the Microsoft Dynamics 365 BC/NAV server. The NAV Connection Manager option will show all Dynamics NAV connection managers that have been created in the current SSIS package. You choose the NAV OData or the NAV REST API connection manager that was created.

Source Object

The Source Object option is only available after a NAV Connection Manager is selected. After you select a NAV Connection Manager, the component will retrieve a list of all available D365 BC/NAV pages & objects.

Child Objects

The Child Objects drop-down option would show any child objects that are available for the specific object that you have chosen. You could choose multiple objects for which the metadata would be available in the output.

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 500. For performance reasons, this parameter should not be too small.

Max Rows Returned (since v22.1)

The Max Rows Returned option allows you to limit the number of rows returned.

  • When specified, the NAV source component will stop paging as soon as the exact number of records has been retrieved.
  • When the option is set to 0, it will retrieve all satisfying records by automatically paging through the entire data set.
Filter

The Filter text box lets you specify filter criteria to help retrieve only the records you specify. The ellipsis (...) button next to the field lets you open up the OData Query Designer window as shown below.

NAV OData Source Query builder.png

OData Query Designer

The OData Query Designer supports adding filter conditions with both static and variable values

  • Add condition (+): Adds a condition to be used in the query.
  • Remove condition (-): Removes the condition that was added.
  • Move Right: Moves the condition right and adds a bracket to the left.
  • Move Left: Moves the condition left and adds a bracket to the right.
  • Operator: Use the query operator to specify how each input value in a clause must relate to the corresponding value in a lookup table.
  • Value: The value for using the condition. Available options are:

    • Variable: Enables the user to perform match based on a System or User SSIS variable

    • Static Value: Enables the user to perform match based on a static value.

  • Remove All: Removes all conditions

Custom Query Mode

Switching to the query designer or Customer Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

NAV OData Source Query builder - Custom.png

Order By
The Order By field can be used to specify the order by clause. Clicking on the ellipsis (...) button would open up the below Order By Builder window.
NAV OData Source OrderBy Builder.png
  • Add condition (+): Adds a field to be used in Order by clause.
  • Remove condition (-): Removes the field that was added.
  • Ascending/Descending: Choose either to be ordered in ascending or descending order.
  • Remove All: Removes all fields from the order by clause.
Refresh Component Button

By clicking the Refresh Component button, the component will generally retrieve the latest metadata from NAV and update each field. Options are available to streamline this process. After clicking the button, the following options will be presented:

NAV Source Component - Refresh Metadata

  • Update all: This would update all the fields regardless of whether there are metadata changes.
  • Update if there are incompatible metadata: This option would refresh any incompatible metadata to the discovered metadata.
  • Update none: No metadata updates would happen to existing fields.

Columns Page

The Columns page of the NAV Source Component shows you all available columns from the object that you specified on the General page.

NAV OData Source Columns.png

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available NAV fields. This is a productive way to check or uncheck all available NAV fields.

Note: As a general best practice, you should only select the NAV fields that are needed for the downstream pipeline components. This would not only speed up NAV queries, but also save resources for the SSIS engine.