Use of NAV Destination Component

The NAV Destination Component is an SSIS data flow pipeline component that can be used to write data to a destination Microsoft Dynamics NAV server. You may create, update, upsert or delete NAV records using the NAV Destination Component. 

The NAV Destination Component includes the following three pages to configure how you want to write data to Microsoft Dynamics NAV server.

  • General
  • Columns
  • Error Handling

The General page is used to specify general settings for the NAV destination component. The Columns page allows you to map the columns from upstream components to NAV fields in the destination entity. The Error Handling page allows you to specify how errors should be handled when they occur. 

General page

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

NAV Destination Editor

NAV Connection Manager

The NAV destination component requires a NAV connection in order to be able to connect to the Microsoft Dynamics NAV server. The NAV Connection Manager option will show all DynamicsNAV connection managers that have been created in the current SSIS package.

Destination Object

The Destination Object option allows you to specify which NAV page or object to write data to.

Action

The Action option allows you to specify how data should be written to the Microsoft Dynamics NAV server. There are currently four (4) supported action types available.

  • Create - Create new record(s) in NAV
  • Update - Update existing record(s) in NAV
  • Delete - Delete record(s) from NAV
  • Upsert - Query for record(s) with specific criteria, if there are matches an update will be performed; if there are no matches, the record(s) will be created in NAV.
Batch Size

The Batch Size option allows you to specify how many records you want to submit to the NAV web service.

Supplementary Parameters Button

When writing to specific objects (ex. General Journal object), special parameters are required. These input values will be specified in the Supplementary Parameters area. The general format looks something like this:

<CurrentJnlBatchName>CASH</CurrentJnlBatchName>

Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.

Map Unmapped Fields Button

By clicking this button, the component will try to map any unmapped NAV fields by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped NAV fields.

Clear All Mappings Button

By clicking this button, the component will reset all your mappings in the destination component.

Upsert Functionality

The Upsert action allows you to update records if they already exist, or create them if they do not exist, all in one component. The Upsert action only supports a batch size of 1. Each record that is fed into the destination component will produce a read service call, and a write service call.

The read service call searches NAV for the given record. If multiple matches are found, you can choose different ways to handle the matches.

  • Update All Records - All records found will be updated in NAV.
  • Update One Record - The first record that is found will be updated in NAV.
  • Ignore All Records - None of the records found will be updated in NAV.
  • Raise an Error - None of the records found will be updated in NAV, and an Error will be fired.

Columns page

The Columns page of the NAV Destination Component allows you to map the columns from upstream components to NAV fields for the destination entity. 

In the Columns page, you would see a grid that contains three columns as shown below.

NAV Destination Editor

  • Input Column - You can select an input column from an upstream component for the corresponding NAV field.
  • Destination NAV Field - The NAV field that you are writing data. 
  • Data Type - This column indicates the type of value for the current NAV field in the NAV system.

*Note - if you hover over any OptionString Fields in the Destination NAV Field column, you will see all the possible OptionString fields that NAV will take as its input.

NAV Destination Editor

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen. 

NAV Destination Editor

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to write to Dynamics NAV will be redirected to the 'Dynamics NAV Destination Error Output' output of the Destination Component. As indicated in the screenshot below, the green output connection represent rows that were successfully written, and the red 'Dynamics NAV Destination Error Output' connection represents rows that were erroneous. The 'NavErrorMessage' output column found in the 'Dynamics NAV Destination Error Output' may contain the error message that was reported by Dynamics NAV or the component itself.

error output

NOTE:  Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.

In the Error Handling page, there is also an option (since v2.0) that can be used to enable or disable the following output fields for the destination component.

  • Key - Contains the newly created NAV record's Key, which you can use to write to log or further process using additional data flow components.
  • NavRecordNo  (since v3.1) - Contains the newly created NAV record's No field value. Note that this field may not be applicable to some entities or actions, in which case, the output will be just NULL.
  • IsNew - Contains value to indicate whether it is a newly created NAV record, or an existing one. This is useful when you use Upsert action.

NOTE: If you don't plan to use any of those fields for any further processing, it is generally recommended to turn them off, so you don't get any warning from SSIS by complaining that those fields are never used, and it should also provide a slightly better performance by doing so. Note again this feature is only available since v2.0.