Overview
Configuration is provided for establishing connections with the Sage Intacct service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
In this task, we will show you step-by-step how to create a connection to Sage Intacct REST API using COZYROC's REST Connection Manager. Please review the Sage Intacct documentation page here. You will need the following information:
- sender_id
- sender_password
- User
- Password
- Company
Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers Tab and Select New Connection. In Add SSIS Connection Manager scroll down and select Connection managers for REST Connection and click Add.
Step 2. From the drop-down menu Configuration select Sage Intacct. Enter the required parameters in the places shown in the screenshot.
The default server host URL is for the production instance.
Step 3. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your Sage Intacct instance.
In this guide, we will show how to read data from the Sage Intacct service resource using the COZYROC REST Source component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3. Double click on the "REST Source" component and select Sage Intacct connection from the combo. Select from available Sage Intacct resources listed in the second combo below.
- Optionally you can include extra parameters in the grid below if needed.
-
You can also provide complex query using JSON parameter. Query should be in JSON format. For Resource that is using Object Specific Function, the Query will be without Object Header that you are invoking to retrieve data
1- Object Specific Function Resource JSON format Query Example { "reportingperiodname": "Calendar Year Ended June 2016", "glaccountno": "xxxx" } 2- Other Resources JSON format Query Example { "read": { "object": "GLACCOUNT", "keys": "1", "fields": "*" } }
Step 4. Click on the "Columns tab" and select Sage Intacct resource columns you want to read.
Step 4. Click on the "Preview tab" to output the desire columns data
Bravo! You have now learned how to read data from Sage Intacct service.
In this guide, we will show how to write data to the Sage Intacct service resource using the COZYROC REST Destination component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.
Step 3. Double click on the "REST Destination" component and select Sage Intacct connection from the combo. Also, select resource action and Sage Intacct resource you want to work on.
Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
If you want to provide input for other resource composite objects, change the input above and map the columns.
Done. You have now learned how to write data into the Sage Intacct resource.
Configuration
Base URL address: https://api.intacct.com/ia/xml/xmlgw.phtml.
-
The authentication uses a session token.
The authentication has the following user-defined parameters:
- sender_id: Required. Specify web service sender id.
- sender_password: Required. Specify web services sender password.
- User: Required. Specify web services user id.
- Password: Required. Specify password.
- Company: Required. Specify company id.
The following request parameters will be automatically processed during the authentication process:
-
application/xml:
{{=json2xml({ request: { control: { senderid: connection.user.sender_id, password: connection.user.sender_password, controlid: Math.random(), uniqueid: 'false', dtdversion: '3.0', includewhitespace: 'false' }, operation: { authentication: { sessionid: token.Access }, content: JSON.parse(request.body.content) } } })}} -
Accept:
application/xml
Documentation: https://developer.intacct.com/web-services/#authentication.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).accountbalance)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('get_accountbalances', parameters)}}
Documentation: https://developer.intacct.com/api/general-ledger/account-balances/#list-account-balances-legacy.
- [Read ByDimension] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).accountbalance)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('get_accountbalancesbydimensions', parameters)}}
Documentation: https://developer.intacct.com/api/general-ledger/account-balances/#list-account-balances-by-dimension.
- glaccountno
Template: ShortText.
- startbalance
Data type: DT_R8
- periodbalance
Data type: DT_R8
- endbalance
Data type: DT_R8
- locationid
Data type: DT_I4
- locationname
Template: ShortText.
- departmentid
Data type: DT_I4
- departmentname
Template: ShortText.
- debits
Data type: DT_R8
- credits
Data type: DT_R8
- adjdebits
Data type: DT_R8
- adjcredits
Data type: DT_R8
- reportingbook
Template: ShortText.
- currency
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).auditTrailDetail)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getAuditTrail', parameters)}}
- userId
Template: ShortText.
- object
Template: ShortText.
- objectKey
Data type: DT_I4
- accessTime
Template: DateTime.
- accessMode
Template: ShortText.
- workflowAction
Template: ShortText.
- webServer
Template: ShortText.
- trackingId
Template: ShortText.
- ipAddress
Template: ShortText.
- source
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).financialSetup)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getFinancialSetup', parameters)}}
- baseCurrency
Template: ShortText.
- defaultBook
Template: ShortText.
- firstFiscalMonth
Data type: DT_I4
- multiBaseCurrency
Data type: DT_BOOL
- multiBook
Data type: DT_BOOL
- multiCurrency
Data type: DT_BOOL
- multiEntityShared
Data type: DT_BOOL
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response.response.operation.result.data.dimensions.dimension)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getDimensions', parameters)}}
- objectName
Template: ShortText.
- objectLabel
Template: ShortText.
- termLabel
Template: ShortText.
- userDefinedDimension
Data type: DT_BOOL
- enabledInGL
Data type: DT_BOOL
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).relationship)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getDimensionRelationships', parameters)}}
- dimension
Template: ShortText.
- object_id
Data type: DT_I4
- autofillrelated
Data type: DT_BOOL
- enableoverride
Data type: DT_BOOL
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).relationship)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getDimensionAutofillDetails', parameters)}}
- dimension
Template: ShortText.
- object_id
Data type: DT_I4
- enableoverride
Data type: DT_BOOL
- autofill
Field components:
-
Uses template:
ShortText. - Contains the following components: dimension, object_id, relationship_id, type.
-
Uses template:
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).RestrictedData)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getDimensionRestrictedData', parameters)}}
- dimension
Template: ShortText.
- value
Template: LongText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response.response.operation.result.data)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('get_applications', parameters)}}
- application
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response.response.operation.result.data.permissions.appSubscription)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getUserPermissions', parameters)}}
- applicationName
Template: ShortText.
- policies
Field components:
- Contains the following components: policy.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {}).araging)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('get_araging', parameters)}}
- customerid
Template: ShortText.
- aging
Field components:
- An array.
- Contains the following components: agingperiod, totalamount, agingdetails.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response.response.operation.result.DdsDdl.Ddl)}}.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
The following request parameters will be automatically processed:
-
application/xml:
{{=Intacct.prepareQuery('getDdsDdl', parameters)}}
- Ddl
Template: LongText.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response.response.operation.result.data[resource.external.typename])}}.
The result is paginated.The action has the following user-defined parameters:
- JSON: Specify JSON input for complex parameters.
- filter: Specify filter in JSON format. Optional
- relationshipFields: Specify comma (,) separated list of relationship fields to include. Optional.
The following request parameters will be automatically processed:
-
application/xml:
{{= function() { var result; if (parameters.get('JSON')) { result = JSON.parse(parameters.JSON); } else { result = { object: resource.external.typename, pagesize: parameters.batchSize, offset: parameters.iterator, select: { field: Intacct.getSelectFields(resource, parameters) }, docparid: (resource.external.typename == 'PODOCUMENT' ? resource.external['_@ttribute'] : undefined), filter: (parameters.get('filter') ? JSON.parse(parameters.filter) : undefined) }; result = { query: _.extend(result, parameters) }; } return { function: _.extend({ '@controlid': Math.random() }, result) }; }() }}
- [Read legacy] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray((response.response.operation.result.data || {})[resource.external.typename.toLowerCase()])}}.
The action uses POST method.
The result is paginated.The action has the following user-defined parameters:
- query: Required. Specify query.
The following request parameters will be automatically processed:
-
application/xml:
{{= function() { var result = { function: { '@controlid': Math.random() } }; if (parameters.iterator) { result.function['readMore'] = { resultId: parameters.iterator }; } else { result.function['readByQuery'] = { object: resource.external.typename, fields: Intacct.getSelectFields(resource, parameters).join(), pagesize: parameters.batchSize, query: parameters.query }; } return result; }() }}
Documentation: https://developer.intacct.com/web-services/queries/#using-legacy-readbyquery.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/xml:
{{= { function: { '@controlid': Math.random(), create: function() { var result = {}; result[resource.external.typename] = item; return result; }() } } }}
-
_includeUserParameters:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/xml:
{{= { function: { '@controlid': Math.random(), update: function() { var result = {}; result[resource.external.typename] = item; return result; }() } } }}
-
_includeUserParameters:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
application/xml:
{{= { function: { '@controlid': Math.random(), delete: { object: resource.external.typename, keys: item.RECORDNO } } } }}
-
application/xml:
- [External]
-
The external fields list is extracted from:
{{=Intacct.getObjectFields(resource, resource.external.typename, parameters, [])}}.
The external resource list URL address: /.
The external resource list is extracted from: {{=Intacct.getObjects(response.response.operation.result.data.type)}}.
Based on resource template Base.
- [Read] action
-
The action uses POST method.
- [Create] action
-
The action uses POST method.
- [Update] action
-
The action uses POST method.
- [Delete] action
-
The action uses POST method.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- Date
Data type: DT_DBDATE
What's New
- New: Improved response processing performance.
- Fixed: Incorrect caching of all object relationship fields, instead of only loading from the list selected in relationshipFields parameter. (Thank you, James).
- New: Support for
create_sotransactionaction. - New: 'Attachment Legacy' resource.
- New: Introduced connection.
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.