COZYROC Sage 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 Sage. They will preserve the integrity of your data by always using the recommended Sage 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 Sage. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components. In addition, COZYROC also provides short and informative demonstration videos, providing step-by-step instructions on how to get started.
The Sage integration package consists of four parts:
- Sage Connection Manager
- Stores the credentials and functionality for connecting to Sage / SalesLogix server.
- Well documented API for use by user-defined scripts.
- Sage Source component
- Retrieves data from a Sage entity.
- Ability to specify query parameters.
- The source can be dynamically modified at runtime using an SSIS expression.
- Sage Destination component
- Inserts or updates data in Sage entity.
- Supports three entity actions: Create, Update or Delete
- Provides information about records with errors as well as the identifier assigned for new records created.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional Sage integration scenarios.
Parameters
Server
Use the Server page of the Sage Connection Manager dialog to specify properties for connecting to Sage SData service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify the name or IP address of the Sage data server.
Specify SData application identifier. The following list of identifiers are known:
Id Application slx SalesLogix sageERP Accpac (not tested) crmj Sage CRM (not tested) x3 Sage X3 (not tested) Specify SData contract identifier. The following list of identifiers are known:
Id Application dynamic SalesLogix accpac Accpac sagecrm Sage CRM erp Sage X3 Specify SData DataSet identifier. The following list of identifiers are known:
Id Application - SalesLogix <company> Accpac - Sage CRM <company> Sage X3 Specify user name to access Sage.
Specify password to access Sage.
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.
Proxy
Use the Proxy page of the Sage 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 Sage Connection?
- How to trace HTTP(S) and REST API using Fiddler
What's New
- Fixed: Various fixes (Thank you, Adam).
- New: Introduced connection.
Related documentation
Overview
Sage Source Component is SSIS Data Flow Component for consuming data from Sage SData Service. The configuration parameters are provided in the standard Data Flow Component dialogs. A separate output is setup for the main entity and each related composite entity.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Sage connection manager.
Related Topics: Sage Connection Manager
Specify the number of rows to be retrieved as a batch.
Select Sage entity.
Specify SData query parameters. Multiple parameters are separated with newline. For example to retrieve account entity records where names start with the letter M and include related Addresses information specify the following parameters:
where=AccountName like 'M%' include=Addresses
Knowledge Base
- Where can I find the documentation for the Sage Source?
- How to modify query parameters dynamically at runtime
- How to work with composite records handling in a Sage source component.
What's New
- New: Introduced component.
Related documentation
Overview
Sage Destination Component is SSIS Data Flow Component for loading data into Sage SData entity. The configuration parameters are provided in the standard Data Flow Component dialogs. A separate input is setup for the main entity and each related composite entity.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Sage connection manager.
Related Topics: Sage Connection Manager
Select destination entity action. This property has the options listed in the following table.
Action Description Create Create new record in the destination entity. Update Update existing record in the destination entity. You have to specify entity key for the update to work. Delete Delete existing record from the destination entity. After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination page metadata.Specify the number of rows to be sent as a batch.
Specify the destination Sage SData entity where the data is loaded.
Specify how to handle rows with errors.
Knowledge Base
- Where can I find the documentation for the Sage Destination?
- How to work with composite records handling in a Sage destination component.
- How to find which records contain errors in Sage destination component.
What's New
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in Sage 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 Sage.SData.Client.dll libraries. Also include CozyRoc.SqlServer.SSIS and CozyRoc.Sage namespaces.
IConnection / ISageConnection
When you call Sage Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with Sage SData service.
Methods
Connects to Sage SData service. The method result is object implementing ISDataService interface.
Closes the connection to Sage SData service. Use always after you finish working with the service.
Returns true if connected to the service.
Gets entity 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 entities, which can be read. 1 Return entities, which support record creation/update. 2 Return entities, which can be deleted.
Gets specified entity composite entities list. These are the method parameters:
- root (string) - entity name.
Get object used to manipulate Sage SData entity. The method returns IEntity object. These are the method parameters:
- name (string) - entity name.
Properties
Specify the name or IP address of the Sage SData server.
Specify application identifier.
Specify contract identifier.
Specify dataset.
Specify user name to access the Sage SData service.
Specify user password to access the Sage SData service.
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.
IProperty / ISageProperty
This interface is used to retrieve Sage SData property information.
Properties
Returns property name.
Returns true if property is entity key.
Returns true if property is read-only.
Returns property type.
Returns property length.
Returns property precision.
Returns property scale.
Returns internal SDataSchemaValueProperty object.
IEntity / ISageEntity
This interface is used for Sage SData entity management.
Methods
Get entity properties. The method returns array of IProperty objects.
Constructs in-memory entity (AtomEntry) instance.
Sets property value in in-memory entity. These are the method parameters:
- entity (object) - in-memory entity instance.
- property (string) - property name.
- value (string) - property value.
Returns property value from in-memory object. These are the method parameters:
- entity (object) - in-memory entity instance.
- property (string) - property name.
Retrieves data from Sage SData service. The result is IEnumerable object. These are the method parameters:
- query (object) - If data is retrieved from regular Sage entity, the query parameter should contain list of SData query parameters. Multiple parameters are separated with newline. If data is retrieved from composite entity, the query parameter should contain entity identifier or object. Multiple identifiers are separated with semicolon (;).
- batchSize (int) - batch size.
Creates record(s) in entity. The result is a list of newly created records key. If record creation fails, error code is reported instead. These are the method parameters:
- entity (object) - single entity or list of entities.
Updates existing record(s) in entity. The result is a list of updated records key. If record update fails, error code is reported instead. These are the method parameters:
- entity (object) - single entity or list of entities.
Updates existing records in composite entity. These are the method parameters:
- entity (object) - Sage entity object.
- id (string) - entity identifier. Multiple identifiers are separated with semicolon (;).
- list (object array) - array of Sage entity objects with update data.
Deletes record(s) in object. The result is a list of deleted records key. If record deletion fails, error code is reported instead. These are the method parameters:
- id (object) - single entity or list of entities.
Properties
Returns the entity name.
Returns true if composite entity.
Knowledge Base
Related documentation
Knowledge Base
- How to use COZYROC Sage Intacct Connector Actions Read, Create, Update and Delete Http (Methods)
- Where can I find the documentation for the Sage Source?
- Where can I find the documentation for the Sage Destination?
- Where can I find the documentation for the Sage Connection?
- How to modify query parameters dynamically at runtime
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.