COZYROC Dynamics NAV components, part of COZYROC SSIS+ suite since 2012, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from Microsoft Dynamics NAV. They will preserve the integrity of your data by always using the recommended Microsoft web services API. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for Dynamics NAV data. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The Dynamics NAV integration package consists of four parts:
- Dynamics NAV Connection Manager
- Stores the credentials and functionality for connecting to an on premise or in-the-cloud Dynamics NAV server.
- Well documented API for use by user-defined scripts.
- Dynamics NAV Source component
- Retrieves data from a Dynamics NAV page.
- The Page or the Page Filter used by the component can be dynamically modified at runtime using a variable and an expression.
- Dynamics NAV Destination component
- Writes data to Dynamics NAV pages.
- Supports three actions: Create, Update or Delete.
- Provides information about records with errors as well as the unique record identifier of the added, updated or deleted records.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional Dynamics NAV integration scenarios.
- Uses the standard Microsoft Dynamics NAV web services API.
Technical Specifications
Case Studies
Overview
Dynamics NAV Connection Manager is SSIS Connection Manager for establishing Dynamics NAV connections. Use the API to build components based on Dynamics NAV Connection Manager.
Parameters
Server
Use the Server page of the Dynamics NAV Connection Manager dialog to specify properties for connecting to Dynamics NAV service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify the name or IP address of the Dynamics NAV server.
Specify user name to access Dynamics NAV.
Specify password to access Dynamics NAV.
Select company.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Advanced (1.6 SR-1)
Use the Advanced page of the Dynamics NAV Connection Manager dialog to specify properties if your Dynamics NAV server configuration is different from the default.
Specify Dynamics NAV instance.
Proxy
Use the Proxy page of the Dynamics NAV Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
- Where can I find the documentation for the Dynamics NAV Connection?
- How to trace HTTP(S) and REST API using Fiddler
- Error Message: The connection type "xxxx" specified for connection manager "xxxx" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
What's New
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Provision of more detailed error message information.
- New: A new InstanceName parameter for setting instance.
- New: Introduced connection.
Related documentation
Overview
The Dynamics NAV Source Component is an SSIS Data Flow Component for consuming data from the Dynamics NAV Service. The configuration parameters are provided in the standard Advanced Editor component dialog.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics NAV connection manager.
Related Topics: Dynamics NAV Connection Manager
Specify the number of rows to be retrieved as a batch.
Select the Dynamics NAV page.
Specify page filter. The filter is specified as a field name, followed by a colon (:) and ends with the filter specification. Multiple filters are separated with a newline character. For example to get all records where the Location_Code field is GREEN, specify: Location_Code:=GREEN
For more information about filter specification check here.
Knowledge Base
- Where can I find the documentation for the Dynamics NAV Source?
- Dynamics NAV Source: How to modify the PageFilter dynamically at runtime
- Dynamics NAV Source: How to modify the Page dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- Fixed: Component failed to process more than one PageFilter (Thank you, Lars).
- New: Introduced component.
Related documentation
Overview
The Dynamics NAV Destination Component is an SSIS Data Flow Component for loading data into the Microsoft Dynamics NAV page. The configuration parameters are provided in the standard Data Flow Component dialogs.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics NAV connection manager.
Related Topics: Dynamics NAV Connection Manager
Select a destination page action. This parameter has the options listed in the following table.
Action Description Create Create a new record in the destination page. Update Update an existing record in the destination page. The appropriate page record key must be specified for the update to work. Delete Delete an existing record from the destination page. The appropriate page record key must be specified for the delete to work. After changing the action, you must use the Refresh command in the 'Column Mappings' tab to reload the destination page metadata.Specify the destination Dynamics NAV page where the data is to be loaded.
Specify the number of rows to be sent as a batch.
Specify how to handle rows with errors.
Contains the unique identifier of the added, updated or deleted record.
Knowledge Base
- Where can I find the documentation for the Dynamics NAV Destination?
- How to find which records contain errors in Dynamics Nav destination component.
- Dynamics NAV Destination: How to modify the Dynamics NAV Destination page dynamically at runtime
What's New
- Fixed: Component failed when the input data contained newline character (Thank you, Lars).
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in Dynamics NAV components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll library. Also include CozyRoc.SqlServer.SSIS and CozyRoc.Dynamics.Nav namespaces.
IConnection / INavConnection
When you call Dynamics NAV Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with Dynamics NAV service.
Connects to Dynamics NAV service.
Closes the connection to Dynamics NAV service. Use always after you finish working with the service.
Returns true if connected to the service.
Returns list of available companies.
Returns list of available pages in selected company.
Returns page fields. The method returns array of IPageField objects. These are the method parameters:
- page (string) - page name
Query Dynamics NAV service. The method returns array of strings, where each data record has the following format:
[field name]:[field value][CR][LF] [field name]:[field value][CR][LF] ...
These are the method parameters:
- page (string) - page name.
- filter (string) - query filter. The filter is specified as field name, followed by colon (:) and ends with filter specification. Multiple filters are separated with newline. For example to get all records where Location_Code field is GREEN, specify: Location_Code:=GREEN For more information about filter specification check here.
- bookmark (string) - query bookmark. This parameter is used when you have multiple batches of data. The bookmark is the last record Key column from current batch. To get initial batch set bookmark to empty.
- size (int) - query batch size.
Creates records in page. The method returns array of newly created records keys. These are the method parameters:
- page (string) - page name.
- data (string array) - array of data. Each data record must be in the following format:
[field name]:[field value][CR][LF] [field name]:[field value][CR][LF] ...
Updates records in page. The method returns array of updated records keys. These are the method parameters:
- page (string) - page name.
- data (string array) - array of data. Each data record must be in the following format:
[field name]:[field value][CR][LF] [field name]:[field value][CR][LF] ...
.
Deletes record in page. The method returns result if record is deleted. These are the method parameters:
- page (string) - page name.
- data (string array) - record key.
Properties
Specify the name or IP address of the Dynamics NAV server.
Specify user name to access the Dynamics NAV service.
Specify user password to access the Dynamics NAV service.
Specify company name.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
| Value | Description |
|---|---|
| True | All clients use same connection (default). |
| False | Every client uses different connection. |
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
IPageField / INavPageField
This interface is used to retrieve page field information.
Properties
Returns field name.
Returns field type.
Returns field length.
Returns field precision.
Returns field scale.
Knowledge Base
Related documentation
Dynamics NAV Connection
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Provision of more detailed error message information.
- New: A new InstanceName parameter for setting instance.
- New: Introduced connection.
Dynamics NAV Destination
- Fixed: Component failed when the input data contained newline character (Thank you, Lars).
- New: Introduced component.
Dynamics NAV Source
- Fixed: Component failed to process more than one PageFilter (Thank you, Lars).
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the Dynamics NAV Destination?
- Where can I find the documentation for the Dynamics NAV Connection?
- Where can I find the documentation for the Dynamics NAV Source?
- Dynamics NAV Source: How to modify the Page dynamically at runtime
- How to find which records contain errors in Dynamics Nav destination component.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.