
COZYROC OpenAir 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 OpenAir. They will preserve the integrity of your data by always using the recommended OpenAir SOAP 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 OpenAir. 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 OpenAir integration package consists of four parts:
- OpenAir Connection Manager
- Stores the credentials and functionality for connecting to OpenAir server.
- Well documented API for use by user-defined scripts.
- OpenAir Source component
- Retrieves data from a OpenAir type.
- Ability to specify input type, filter and method criteria.
- The source can be dynamically modified at runtime using an SSIS expression.
- OpenAir Destination component
- Inserts or updates data in OpenAir type.
- Supports four entity actions: Create, Update, Delete or Upsert
- 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 OpenAir integration scenarios.
Parameters
Server
Use the Server page of the OpenAir Connection Manager dialog to specify properties for connecting to OpenAir service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify the address of the OpenAir service instance. The sandbox instance has the format: https://[company].app.sandbox.openair.com
Specify user name to access OpenAir.
Specify password to access OpenAir.
Specify API Key to access OpenAir.
Specify company to access OpenAir. Contact the OpenAir Support Department or your account representative to request API access.
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 OpenAir Connection Manager dialog to to specify additional properties if your server configuration is different from the default.
- Note: This parameter has been removed in version 2.0.
Select connection type. This property has the options listed in the following table.
Value Description Production Connects to production instance of OpenAir service. Sandbox Connects to sandbox (testing) instance of OpenAir service. Specify API namespace to access OpenAir.
Proxy
Use the Proxy page of the OpenAir 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 OpenAir Connection?
- 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: Updated SOAP proxy with latest definitions (September 2024).
- New: Included UserWorkschedule type.
- New: A new parameter ServerHost to support the new OpenAir service host requirement. The old ConnectionType parameter is no longer relevant and has been removed.
- New: Introduced connection.
Related documentation
Overview
OpenAir Source Component is SSIS Data Flow Component for consuming data from OpenAir Service. 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 OpenAir connection manager.
Related Topics: OpenAir Connection Manager
Specify type association to initialize custom fields for it.
Specify read attributes. Multiple attributes are separated with newline, using syntax: [name]=[value]. Use attributes listed in the following table.
Attribute Value Description deleted 1 Returns deleted records. include_flags 1 Returns account or user switches, by default those are not populated. include_nondeleted 1 Returns all records, deleted and nondeleted. This attribute only works in conjunction with the deleted attribute.with_project_only 1 Used only with type: Customer. Will only return customers which have associated project records. base_currency 3 Letter currency code. Works with type: Currencyrate. Converts values on the fly to currency specified. generic 1 Returns generic resources (users) only, where by default, the API returns regular users only. Specify the number of rows to be retrieved as a batch. Default is 1000.
Specify records filter. Multiple filters are separated with newline. Use filters listed in the following table.
Value Description open-envelopes Returns only records associated with an open envelope. approved-envelopes Returns only records associated with an approved envelope. rejected-envelopes Returns only records associated with a rejected envelope. submitted-envelopes Returns only records associated with a submitted envelope. nonreimbursed-envelopes Returns envelopes that have a non-zero balance attribute. reimbursable-envelope Returns only records associated with a reimbursable envelope. open-slips Returns only records associated with an open slip. approved-slips Returns only records associated with an approved slip. open-timesheets Returns only records associated with an open timesheet. approved-timesheets Returns only records associated with an approved timesheet. rejected-timesheets Returns only records associated with a rejected timesheet. submitted-timesheets Returns only records associated with a submitted timesheet. not-exported Returns only records that have not been marked as exported. approved-revenue-recognition-transactions Returns only revenue recognition transactions belonging to approved revenue_container records. - newer-than
- older-than
- date-equal-to
- date-not-equal-to
Date filters. Date filter parameters and multiple date filters are separated with comma. Not supported in importexport type. Example: to retrieve all records where updated field is equal or greater than Jan 1, 2011, set for filter:
date-equal-to,newer-than,updated,updated,01-01-2011,01-01-2011Example: to retrieve all records for the whole day and times use:
newer-than,older-than,updated,updated,2011-08-31 23:59:59,2011-09-02Specify to include custom fields in result. This property has the options listed in the following table.
Value Description True The custom fields are included in the result. False The custom fields are not included in the result (default). Deprecated in SSIS+ 1.6 SR-3. Use AssociationCustom parameter instead.Specify to include all records in result, including the deleted. This property has the options listed in the following table.
Value Description True All records are included in the result, including the deleted. False The deleted records are not included in the result (default). Deprecated in SSIS+ 1.6 SR-3. Use Attributes parameter instead.Select input OpenAir type.
After input type change, you have to use Refresh command in the 'Column Mappings' tab to reload type metadata.Select OpenAir retrieve method. This property has the options listed in the following table.
Value Description All Returns all available records (default). EqualTo Returns records that have fields that are equal to the field value(s) passed in MethodCriteria. Selecting this value displays the dynamic option MethodCriteria. EqualTo Returns records that have fields that are not equal to the field value(s) passed in MethodCriteria. Selecting this value displays the dynamic option MethodCriteria. Select variable containing recordset object with method criteria(s) or specify method criteria. To match match multiple values, set field criteria to comma delimited list. To match empty value, set field criteria to empty.
Knowledge Base
- Where can I find the documentation for the OpenAir Source?
- OpenAir Source: How to modify the InputType parameter dynamically at runtime
- OpenAir Source: How to modify the Filter parameter 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
- New: A new parameter AssociationCustom. IncludeCustom parameter is deprecated.
- New: A new parameter Attributes.
- Fixed: Component failed to retrieve custom fields for Costcenter type (Thank you, Tas).
- New: Introduced component.
Related documentation
Overview
OpenAir Destination Component is SSIS Data Flow Component for loading data into OpenAir type. 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 OpenAir connection manager.
Related Topics: OpenAir Connection Manager
Select destination record action. This property has the options listed in the following table.
Action Description Create Create new record in the destination type. Modify Modify existing record in the destination type. You have to specify id field for the update to work. Delete Delete existing record from the destination type. Upsert Update and insert record in the destination type. You have to specify externalid for action to work. After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination type metadata.Specify type association to initialize custom fields for it.
Specify the destination OpenAir type where the data is 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 OpenAir Destination?
- How to find which records contain errors in OpenAir destination component.
- OpenAir Destination: How to modify the OpenAir Destination type dynamically at runtime
What's New
- New: A new parameter AssociationCustom.
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in OpenAir 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.NetSuite.dll libraries. Also include CozyRoc.SqlServer.SSIS, CozyRoc.NetSuite.OpenAir and CozyRoc.NetSuite.OpenAir.Service namespaces.
IConnection / IOpenAirConnection
When you call OpenAir Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with OpenAir service.
Methods
Connects to OpenAir service. The method result is object implementing OpenAir SOAP web services.
Closes the connection to OpenAir service. Use always after you finish working with the service.
Returns true if connected to the service.
Returns list of available OpenAir types.
Get OpenAir type custom fields. The result is array of oaCustField objects. These are the method parameters:
- type (string) - OpenAir type.
Properties
Specify connection type. This property has the options listed in the following table.
Value | Description |
---|---|
0 | Connects to production instance of OpenAir service. |
1 | Connects to sandbox (testing) instance of OpenAir service. |
Specify user name to access the OpenAir service.
Specify user password to access the OpenAir service.
Specify API namespace to access the OpenAir service.
Specify API key to access the OpenAir service.
Specify company to access the OpenAir 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.
Knowledge Base
Related documentation
OpenAir Connection
- New: Updated SOAP proxy with latest definitions (September 2024).
- New: Included UserWorkschedule type.
- New: A new parameter ServerHost to support the new OpenAir service host requirement. The old ConnectionType parameter is no longer relevant and has been removed.
- New: Introduced connection.
OpenAir Source
- New: A new parameter AssociationCustom. IncludeCustom parameter is deprecated.
- New: A new parameter Attributes.
- Fixed: Component failed to retrieve custom fields for Costcenter type (Thank you, Tas).
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the OpenAir Connection?
- Where can I find the documentation for the OpenAir Destination?
- Where can I find the documentation for the OpenAir Source?
- OpenAir Source: How to modify the InputType parameter dynamically at runtime
- OpenAir Source: How to modify the Filter parameter dynamically at runtime
Related documentation
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.