Installation

To install and run SSIS Integration Toolkit for Microsoft Dynamics NAV, your system must have the following components installed. 

  • A supported SSIS design-time or run-time, which can be one of the following:
    • SSIS 2016
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.

    Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.

    For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

    • When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version. 
  • .NET Framework 3.5
    • For any version before v3.2, .NET Framework 3.5 is required. For v3.2 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
      • If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
      • If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
      • If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
  • Windows Installer 4.5
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page you will find two download links. One is for x86 and one is for x64 systems. Make sure to choose and download the right package for your system. 

Download options

Once you click the download link, save the zip file which contains the .msi file. Double click the .msi file to start the setup. A security warning may appear. You can safely accept the warning and continue the setup.

The SSIS Integration for Microsoft Dynamics NAV Setup Wizard will appear. Click ‘Next’ to continue.

Nav Integration Installation

Select the checkbox to agree to the terms of the End-User License Agreement. Click 'Next' to continue.

Nav Integration Installation

Next, select one of the three setup types that best suits your needs.

Nav Integration Installation

Click ‘Install’ to begin the installation.

Nav Integration Installation

To complete the installation, select 'Finish'.

Nav Integration Installation

Prerequisites - Enabling Integration and Synchronization in Dynamics NAV

In order to make the integration possible, your Dynamics NAV server must meet the following requirements. 

  • SOAP Services must be enabled in Microsoft Dynamics NAV Administration program

    Enable SOAP Services

  • Synchronization  must be enabled in Microsoft Dynamics NAV.
  • The following service(s) must be started
    • Microsoft Dynamics NAV Server
    • One of the following services depending on which version of Microsoft Dynamics NAV you are running
      • Microsoft Dynamics NAV Business Web Services, if you are using Dynamics NAV 2009 R2 or earlier
      • World Wide Web Publishing Service, if you are using Dynamics NAV 2013 or later

To turn on integration and enable synchronization, please use the procedures below.

  1. Start Microsoft Dynamics NAV and in the search box, enter Marketing Setup to search.
  2. Open the Marketing Setup window.
  3. Open up the Synchronization FastTab, select the Enable Connector check box. Click Yes to confirm.

    Enable Synchronization

Setup a NAV Connection using NAV Connection Manager

NAV Connection Manager is an SSIS connection manager that can be used to establish connections with the Microsoft Dynamics NAV Server. 

The NAV connection manager allows you to specify how you want to connect to your Dynamics NAV server. 

To add a NAV connection to your SSIS package, right-click in Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window where you can select the "DynamicsNAV" item to add.

new connection

Add NAV Connection Manager

The NAV Connection Manager contains the following three pages which configures how you want to connect to the Microsoft Dynamics NAV server.

  • General
  • Advanced Settings
  • More Info

General page

The General page on the NAV Connection Manager allows you to specify general settings for the connection.

NAV Connection Manager

NAV Server

The NAV Server field lets you specify the location of your Dynamics NAV web service. Make sure to have the HTTP port included. The format of this configuration should be NavServerName:portnumber, ex: navsvr:7047.

Server Version

The Server Version field lets you choose the version of Dynamics NAV that you are running. There are currently four options:

  • Dynamics NAV 2016
  • Dynamics NAV 2015
  • Dynamics NAV 2013 R2 or 2013
  • Dynamics NAV 2009 R2 or 2009
Service URL

Using the Service URL option, you can provide an alternative NAV web service URL which you might have set up in the way that's different from the default.

The Service URL should be in the following format:

http://<NavServerName>:port/InstanceName/WS

 or

https://<NavServerName>:port/InstanceName/WS
User Name

The User Name option allows you to specify the user account that you want to use to connect to your NAV server. Depending on how the NAV connection is used, the user account needs to have proper privileges in your NAV system . 

Password

The Password option allows you to specify the password for the above user account in order to login to your NAV server.

NOTE:  The Password is not included in the NAV connection manager's ConnectionString property by default. This is done by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be Password=myPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString. 

Company

After the NAV server location and login credentials have been provided, click the drop down button of the Company option to show a list of available companies that the user has access to. Select the company that is intended to be used. 

Timeout (secs)

The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 120 seconds.

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully login to the NAV server.

Advanced Settings page

The Advanced Settings page of NAV Connection Manager allows you to specify some advanced settings of the connection. 

NAV Connection Manager

Retry on Intermittent Errors

This is an option designed to help recover from possible intermittent outages or disruption of service. It prevents the integration process from stopping due to temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: We have designed our retry feature carefully such that the retry should only occur when it is deemed safe to do so; however, in some occasions such retry service calls could result in the creation of duplicate data.

Proxy Server

Using the Proxy Server option, you can provide a proxy server to connect to the NAV server.

Port

The Port option allows you to specify port number of the proxy server for the connection.

Username (Proxy Server Authentication)

The Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

The Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

NOTE:  The Proxy Password is not included in the NAV connection manager's ConnectionString property by default. This is done by design for security reasons. However you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

More Info page

The More Info page shows some basic information about the toolkit. In this page, you can find the version information of the toolkit.

NAV Connection Manager

Add NAV Components to SSIS Toolbox

SSIS Integration Toolkit for Microsoft Dynamics NAV includes two data flow components. They must be shown in SSIS toolbox before you can use (drag and drop) them in SSIS data flow task.

  • If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
  • If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.

    SSIS Toolbox

Add NAV Data Flow Components to SSIS Toolbox (SQL Server 2008 R2 or earlier)

In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

Choose items

You will be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select Dynamics NAV Destination and Dynamics NAV Source components from the list. 

Add NAV SSIS Components

Both data flow components should now appear in your SSIS Toolbox, where you can drag and drop any of them to the design surface of your SSIS data flow task. 

Use of NAV Source Component

The NAV Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from the Microsoft Dynamics NAV server.

The NAV Source Component includes the following two pages to configure how you want to read data from Microsoft Dynamics NAV.

  • General
  • Columns

General page

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

NAV Source Editor

NAV Connection Manager

The NAV source component requires a NAV connection in order to connect with 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.

Batch Size

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

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 NAV pages & objects.

NOTE : You will only be able to see those page objects that you have enabled for integration. If you don't see a page object in the list, you would have to add a new Web Service entry for that page object in Dynamics NAV application by going to Web Services module.

Page Filtering

The Page Filtering text box lets you specify filter criteria to help retrieve only the records you specify. The following is a sample of such filter.

<filter>
	<Field>Name</Field><Criteria>ABC*</Criteria>
</filter>
<filter>
	<Field>AccountNumber</Field><Criteria>C002*</Criteria>
</filter>

Please see http://msdn.microsoft.com/en-us/library/hh879066(v=nav.71).aspx for more info on NAV filters.

Page Filtering now supports the use of User and System Variables. Simple select a variable under the Insert Variables drop down menu, and a placeholder value will be inserted into the filter text.

Supplementary Parameters

When working with specific object, there may be special parameters required in order to read from these objects (ex. General Journal object). 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.

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 Source Editor

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.

License Manager

The SSIS Integration Toolkit comes with a license manager program which helps you manage and activate the product license key to be used for the toolkit.

Without a commercial license, SSIS Integration Toolkit will operate under the Developer License which is free to use for development or evaluation purposes. Under the developer license, you can use the software within the development tool (SSDT, BIDS, or Visual Studio). 

KingswaySoft License Manager

The only limitation with the free developer license is that you cannot run the software outside of the development tool (SSDT, BIDS, or Visual Studio). If you would like to run the software outside the development tool, such as running SSIS packages on a scheduled basis or from a command line, you will need to acquire a license from us.

If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch License Manager program, then click "Change License Key" button, where you can request for a free trial license after filling out the necessary Licensee Information.

Adding License Key

If you have received a product license key from us after placing an order through our online shopping cart system, you can also click "Change License Key" button, so that you can enter the product license key in order to activate the software to use the fully-featured commercial license. 

To request a free trial license or activate a product license key that you have received, you can use the Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when the Web Service option is used, which is the option that we recommend.

Alternatively, you can choose the Email option. The license manager will generate an email which you can send to us. The Email option should only be used if your system has no Internet access, and requires our team to manually process your order, so please expect to wait 24 to 48 hours before receiving a license file from us. Once you have received the license file from us through email, you can save it to a local file, which you can then install by clicking "Install License File..." button in License Manager.

If you have acquired a perpetual license from us, after your have activated the software as shown above, your license manager will look something like below.

KingswaySoft License Manager with Commerical License

Since v2.0, you can see your Support Expiry Date or Maintenance Expiry Date in the License Manager program if you are using a perpetual license. By default, your perpetual license comes with one-year maintenance and upgrade, which entitles you to use any version of the software released before your Support Expiry Date or Maintenance Expiry Date. You can extend your support expiry date by entering a new maintenance license key that you have acquired from us.

Add Maintenance Key

If your commercial license is a subscription license, you will not see the Support Expiry Date or Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period.

NOTE: Starting from v2.0, you need to run License Manager program under a local administrative account due to the privileges required to write license file to the system.

Contact Us

If you need any further assistance with the toolkit, please don't hesitate to contact us