Overview
Configuration is provided for establishing connections with Acumatica ERP 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 Acumatica REST API using COZYROC's REST Connection Manager.
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 drop-down menu Configuration select Acumatica. Set [host] to the desired host in the Server host parameter. Then click New in the Token File parameter to create a new token file.
Step 3: Provide client_id and client_secret. Then press 'Next' button.
Step 4: Provide Username and Password. Then press 'Sign in' button.
Step 5: Save generated access token to file. Specify token protection password. Click OK.
Step 6. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your Acumatica instance.
In this guide, we will show how to read data from the Acumatica 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 Acumatica connection from the combo. Select from available Acumatica resources listed in the second combo below.
Optionally you can include extra parameters in the grid below if needed.
Step 4. Click on the "Columns tab" and select Acumatica Resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from Acumatica service.
In this guide, we will show how to write data to the Acumatica 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 Acumatica connection from the combo. Also, select resource action and Acumatica 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 Acumatica resource.
Configuration
Base URL address: https://[host]/[DB]/entity/Default/20.200.001.
- OAuth 2
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}.The authentication has the following user-defined parameters:
- client_id: Required. Specify client ID.
- client_secret: Required. Specify client secret.
- redirect_uri: Required. Specify redirect_uri.
- scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://help-2019r1.acumatica.com/(W(2))/Help?ScreenId=ShowWiki&pageid=ff780860-09c2-46c9-bdd7-c6c3b1fc442c.
- [Read] action
-
Endpoint URL address:
/{{=parameters.entity}}/{{=parameters.entity_id}}.
The result is extracted from:{{=response.files}}.The action has the following user-defined parameters:
- entity: Required. Specify the entity name.
- entity_id: Required. Specify entity identifier.
The following request parameters will be automatically processed:
-
$expand:
files
- [Create] action
-
Endpoint URL address:
/{{=parameters.entity}}/{{=item.entity_id}}/files/{{=item.filename}}.
The action uses PUT method.The action has the following user-defined parameters:
- entity: Required. Specify the entity name.
The following request parameters will be automatically processed:
-
file:
{{=item.content}}
- id
Template: ShortText.
- filename
Template: ShortText.
- href
Template: ShortText. A read-only field.
- content
Data type: DT_IMAGE The value is gathered from
{{=new Uri(connection.serverHost).authority() + item.href}}address.- entity_id
Template: ShortText.
- [Read] action
-
Endpoint URL address:
/{{=resource.external['name']}}.
The result is extracted from:{{= response }}.The action has the following user-defined parameters:
- $filter:
- $custom:
The following request parameters will be automatically processed:
-
$custom:
{{= parameters.get('$custom') }} -
$filter:
{{= parameters.get('$filter') }}
- [Read Single] action
-
Endpoint URL address:
/{{=resource.external['name']}}/{{=parameters.id}}.
The result is extracted from:{{= [response] }}.The action has the following user-defined parameters:
- id: Required. Specify the guid.
- [Create] action
-
Endpoint URL address:
/{{=resource.external.name}}/{{=parameters.actionName}}.The action has the following user-defined parameters:
- actionName: Required. Specify the endpoint Action Name to be executed.
- [Update] action
-
Endpoint URL address:
/{{=resource.external.name}}.
- [Delete] action
-
Endpoint URL address:
/{{=resource.external['name']}}/{{=item.id}}.
- id
A key field. Template: ShortText.
- files
Field components:
- An array.
- Contains the following components: id, filename, href.
- parameters
Template: LongText.
- [External]
-
The external fields URL address:
/{{=resource.external.name}}/$adHocSchema. The external fields list is extracted from:{{=Acumatica.getEntityFields(response)}}. - [Read] action
-
Endpoint URL address:
/{{=resource.external['name']}}.
The result is extracted from:{{= [response] }}.The action has the following user-defined parameters:
- $filter:
- $custom:
The following request parameters will be automatically processed:
-
$custom:
{{= parameters.get('$custom') }} -
$filter:
{{= parameters.get('$filter') }}
- [Read Single] action
-
Endpoint URL address:
/{{=resource.external['name']}}/{{=parameters.id}}.
The result is extracted from:{{= [response] }}.The action has the following user-defined parameters:
- id: Required. Specify the guid.
- [Create] action
-
Endpoint URL address:
/{{=resource.external.name + (parameters.get('actionName') ? '/' + parameters.actionName : '')}}.The action has the following user-defined parameters:
- actionName: Specify the endpoint Action Name to be executed. Optional.
- [Update] action
-
Endpoint URL address:
/{{=resource.external.name}}.
- [Delete] action
-
Endpoint URL address:
/{{=resource.external['name']}}/{{=item.id}}.
- parameters
Template: LongText.
- [External]
-
The external fields URL address:
/{{=resource.external.name}}/$adHocSchema. The external fields list is extracted from:{{=Acumatica.getEntityFields(response)}}.
The external resource list is extracted from: {{=_.filter(Acumatica.getEntities(), function(item) { return item.template == 'Base'; })}}.
Based on resource template Base.
The external resource list is extracted from: {{=_.filter(Acumatica.getEntities(), function(item) { return item.template == 'Inquiry'; })}}.
Based on resource template Inquiry.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
$skip:
{{=parameters.iterator}} -
$top:
{{=parameters.batchSize}} -
$select:
{{=Acumatica.getSelectFields(parameters).join()}} -
$expand:
{{=Acumatica.getExpandFields(resource, parameters)}} -
_includeUserParameters:
{{=parameters}}
-
$skip:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=Acumatica.getParameterBody(item)}}
-
_includeUserParameters:
- [Update] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=Acumatica.setParameterBody(item)}}
-
_includeUserParameters:
- [Delete] action
-
The action uses DELETE method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=Acumatica.setParameterBody(item)}}
-
_includeUserParameters:
- [Read] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
$select:
{{=Acumatica.getSelectFields(parameters).join()}} -
$expand:
{{=Acumatica.getExpandFields(resource, parameters)}} -
application/json:
{{=Acumatica.setInquiryParameterBody(parameters)}}
-
$select:
- [Create] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Update] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Delete] action
-
The action uses DELETE 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: 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.