COZYROC Dynamics GP 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 GP. 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 GP 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 GP integration package consists of four parts:
- Dynamics GP Connection Manager
- Stores the credentials and functionality for connecting to an on premise or in-the-cloud Dynamics GP server.
- Well documented API for use by user-defined scripts.
- Dynamics GP Source component
- Retrieves data from a Dynamics GP Object.
- XML criteria may be used to selectively read records.
- Handles composite Objects by setting up one output for the main objects and a separate output for the related composite objects.
- Choose whether to return just Object summaries or the complete Object data.
- The Object and the XML criteria can be modified at runtime using variables and an expression.
- Dynamics GP Destination component
- Writes data to Dynamics GP Objects.
- Supports four actions on Objects: Create, Update, Delete or Void.
- Provides information about records with errors as well as the record identifier for updated records, deleted records, voided records, and new records.
- The component is able to load composite Objects into the Dynamics GP service.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional Dynamics GP integration scenarios.
- Uses the standard Microsoft Dynamics GP web services API.
Technical Specifications
Case Studies
Overview
Dynamics GP Connection Manager is SSIS Connection Manager for establishing Dynamics GP connections. Use the API to build components based on Dynamics GP Connection Manager.
Parameters
Server
Use the Server page of the Dynamics GP Connection Manager dialog to specify properties for connecting to Dynamics GP service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify the name or IP address of the Dynamics GP server.
Specify user name to access Dynamics GP.
Specify password to access Dynamics GP.
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
Use the Advanced page of the Dynamics GP Connection Manager dialog to specify properties if your Dynamics GP configuration is different from default.
Specify if you want to use the security settings for a different user, other than the user setup in your credentials. The default value is empty.
Select culture in use. Based on the selected culture, the service will return data in format specific to it.
Select how currency information is handled. This property has the options listed in the following table.
Value Description Transactional The transaction currency amount will be used (default). Local The local currency amount will be used. Specify role used for policy and behavior options. If not specified, the default role will be used.
Proxy
Use the Proxy page of the Dynamics GP 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 GP Connection?
- How to trace HTTP(S) and REST API using Fiddler
What's New
- New: Introduced connection.
Related documentation
Overview
Dynamics GP Source Component is SSIS Data Flow Component for consuming data from Dynamics GP Service. The configuration parameters are provided in the standard Data Flow Component dialogs. A separate output is setup for the main object and each related composite object.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics GP connection manager.
Related Topics: Dynamics GP Connection Manager
Select Dynamics GP object.
Specify criteria XML. The criteria should be the same XML format used to serialize Dynamics GP restrictions. For example to retrieve all employees from the sales department, use the following criteria:
<EmployeeCriteria> <DepartmentId xmlns="http://schemas.microsoft.com/dynamics/gp/2006/01"> <EqualValue>SALE</EqualValue> </DepartmentId> </EmployeeCriteria>Specify whether object summary or complete object is returned. This property has the options listed in the following table.
Value Description True Only object summary properties are retrieved. Default. Using this option will improve reading performance significantly. False All object properties are retrieved.
Knowledge Base
- Where can I find the documentation for the Dynamics GP Source?
- How to work with composite records handling in a Dynamics GP source component.
- Composite records handling
- Dynamics GP Source: How to modify source object criteria 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 returned only first 1000 records (Thank you, Patricia).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Related documentation
Overview
Dynamics GP Destination Component is SSIS Data Flow Component for loading data into a Dynamics GP object. The configuration parameters are provided in the standard Data Flow Component dialogs. Composite Objects are supported.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics GP connection manager.
Related Topics: Dynamics GP Connection Manager
Select destination object action. This property has the options listed in the following table.
Action Description Create Create new record in the destination Dynamics GP object. Update Update existing record in the destination Dynamics GP object. You have to specify object identifier for the update to work. Delete Delete existing record from the destination Dynamics GP object. Void Void existing record from the destination Dynamics GP object. After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination page metadata.Specify the destination Dynamics GP object where the data is loaded.
Specify how to handle rows with errors.
Knowledge Base
- Where can I find the documentation for the Dynamics GP Destination?
- How to work with composite records handling in a Dynamic GP destination component.
What's New
- Fixed: Component failed with error "Invalid cast from '...' to 'CozyRoc.Dynamics.Gp.Service.Amount'" when processing GL Transaction object (Thank you, John).
- Fixed: Component failed to report errors in 'Error Output' (Thank you, Adriana).
- Fixed: Numerous fixes and enhancements (Thank you, Patricia).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in Dynamics GP 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 and CozyRoc.Dynamics.dll libraries. Also include CozyRoc.Dynamics.Gp and CozyRoc.Dynamics.Gp.Service namespaces.
IConnection / IGpConnection
When you call Dynamics GP Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with Dynamics GP service.
Methods
Connects to Dynamics GP service.
Closes the connection to Dynamics GP service. Use always after you finish working with the service.
Returns true if connected to the service.
Get Dynamics GP web service object.
Get Dynamics GP context object.
Returns list of available companies.
Gets objects list for specific operation. These are the method parameters:
- operation (uint) - It can be one of the options listed in the following table.
Value Description 0 Return objects, which can be completely read. 1 Return objects, which support summary read. 2 Return objects, which support record creation. 3 Return objects, which can be updated. 4 Return objects, which permit record removal. 5 Return objects, which permit record void.
Gets specified object composite objects list. These are the method parameters:
- root (string) - object name.
Get object used to manipulate Dynamics GP object. The method returns IObject object. These are the method parameters:
- name (string) - object name.
Properties
Specify the name or IP address of the Dynamics GP server.
Specify user name to access the Dynamics GP service.
Specify user password to access the Dynamics GP 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 you want to use the security settings for a different user, other than the user setup in your credentials. The default value is empty.
Specify culture in use. It should be in the format en-US. Based on the selected culture, the service will return data in format specific to it.
Specify how currency information is handled. This property has the options listed in the following table.
| Value | Description |
|---|---|
| 0 | The transaction currency amount will be used (default). |
| 1 | The local currency amount will be used. |
Specify role used for policy and behavior options. If not specified, the default role will be used.
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.
IProperty / IGpProperty
This interface is used to retrieve Dynamics GP object property information.
Properties
Returns property name.
Returns true if property is object key.
Returns property type.
Returns property length.
Returns property precision.
Returns property scale.
IObject / IGpObject
This interface is used for Dynamics GP object management.
Methods
Get object properties. The method returns array of IProperty objects. These are the method parameters:
- summary (bool) - if true, the summary object properties are returned. Otherwise it will return the complete object properties.
Constructs Dynamics GP object instance. These are the method parameters:
- summary (bool) - if true, summary object is returned. Otherwise it will return the complete object.
Sets property value in Dynamics GP object. These are the method parameters:
- instance (object) - Dynamics GP object instance.
- property (string) - property name.
- value (object) - property value.
Returns property value from Dynamics GP object. These are the method parameters:
- instance (object) - Dynamics GP object instance.
- property (string) - property name.
Retrieves Dynamics GP object. These are the method parameters:
- id (object) - object identifier.
Retrieves objects from Dynamics GP service. These are the method parameters:
- criteriaXML (string) - XML string of Dynamics GP restriction.
- summary (bool) - if true, summary objects are returned. Otherwise it will return the complete objects.
Retrieves records of composite object. These are the method parameters:
- instance (object) - instance to main Dynamics GP object.
- key (string) - parent object key. Multiple keys are separated with semicolon (;).
Creates record in object. These are the method parameters:
- instance (object) - Dynamics GP object instance with create data.
Updates existing record in object. These are the method parameters:
- instance (object) - Dynamics GP object instance with update data.
Updates records in composite object. These are the method parameters:
- instance (object) - instance to main Dynamics GP object.
- key (string) - parent object key. Multiple keys are separated with semicolon (;).
- list (object array) - array of composite Dynamics GP objects.
Deletes record in object. These are the method parameters:
- id (object) - identifier of record to delete.
Voids record in object. These are the method parameters:
- id (object) - identifier of record to void.
Properties
Returns the object name.
Returns true if composite object.
Knowledge Base
Related documentation
Dynamics GP Destination
- Fixed: Component failed with error "Invalid cast from '...' to 'CozyRoc.Dynamics.Gp.Service.Amount'" when processing GL Transaction object (Thank you, John).
- Fixed: Component failed to report errors in 'Error Output' (Thank you, Adriana).
- Fixed: Numerous fixes and enhancements (Thank you, Patricia).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Dynamics GP Source
- Fixed: Component returned only first 1000 records (Thank you, Patricia).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the Dynamics GP Destination?
- Where can I find the documentation for the Dynamics GP Connection?
- Where can I find the documentation for the Dynamics GP Source?
- How to work with composite records handling in a Dynamics GP source component.
- Composite records handling
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.