Sage

Introducing the COZYROC SSIS+ library with support for integration and migration of Sage data. Available on the market since 2012, COZYROC makes it easy to integrate or migrate data to and from Sage and 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.

What's New

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 source component dialog. A separate output is setup for the main entity and each related composite 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

FAQ

How to modify query parameters dynamically at runtime

The source entity and query parameters are available in the data flow properties list. Follow these steps to setup expression:

Right-click on data flow canvas and select Properties menu.

Scroll-down and find property named like [Sage].[QueryParameters]. This is the property containing the query parameters.

The composite record output contains not only properties from the composite entity, but also the parent entities. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

What's New

1.6

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 destination component dialog. A separate input is setup for the main entity and each related composite entity.

Parameters

Configuration

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.

BatchSize

Specify the number of rows to be sent as a batch.

DestinationEntity

Specify the destination Sage SData entity where the data is loaded.

ErrorRowDisposition (Sage Destination Input)

Specify how to handle rows with errors.

FAQ

Composite records handling

The composite record input accepts sets of records, one set for each parent record. Each set of records must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

What's New

1.6

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 ManagerAcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with Sage SData service.

Methods

Connect method

Connects to Sage SData service. The method result is object implementing ISDataService interface.

Close method

Closes the connection to Sage SData service. Use always after you finish working with the service.

IsConnected method

Returns true if connected to the service.

GetEntityList method

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.

Methods

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.

GetPropertyValue method

Returns property value from in-memory object. These are the method parameters:

entity (object) - in-memory entity instance.

property (string) - property name.

Read method

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.

Create method

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.

Update method

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.

UpdateComposite method

Updates existing records in composite entity. These are the method parameters:

The composite record input accepts sets of records, one set for each parent record. Each set of records must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The composite record output contains not only properties from the composite entity, but also the parent entities. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

Ready to give it a try?

COZYROC SSIS+ Components Suite is free for testing in your development environment.