Overview
Configuration is provided for establishing connections with the Expensify 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 Expensify 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 Expensify connector.
Step 3. In the grid below specify values for partnerUserID and partnerUsersecretSet parameters.
Step 4. Click "Test Connection" to test your connection.
Congratulations! You have now established a successfull connection to your Expensify instance.
In this guide, we will show how to read data from the Expensify 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 Expensify connection from the combo. Select from available Expensify resources listed in the second combo below.
Specify the parameter if required in the grid below in the required format.
Step 4. Click on the "Columns tab" and select Expensify 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 Expensify service.
In this guide, we will show how to write data to the Expensify 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 Expensify connection from the combo. Also, select resource action and Expensify 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 Expensify resource.
Configuration
Base URL address: https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations.
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- partnerUserID: Required. Specify the partnerUser identifier.
- partnerUserSecret: Required. Specify the partnerUser secret.
The following request parameters will be automatically processed during the authentication process:
-
requestJobDescription:
{{=JSON.stringify(_.extend( JSON.parse(request.parameters.requestJobDescription), { credentials: { partnerUserID: connection.user.partnerUserID, partnerUserSecret: connection.user.partnerUserSecret } } ))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#authentication.
Based on resource template Base.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=Expensify.prepareWriteJobRequest('create', 'report', item)}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#report-creator.
-
requestJobDescription:
- policyID
Template: ShortText.
- report
Field components:
-
Uses template:
ShortText. - Contains the following components: title, fields.
-
Uses template:
- employeeEmail
Template: ShortText.
- expenses
Field components:
- An array.
- Contains the following components: date, currency, merchant, amount.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=Expensify.getCSVData(parameters, response)}}.The action has the following user-defined parameters:
- type: Required. Specify report type.
- template: Required. Specify template parameter to format the Expensify data as you wish.
- reportState: Only the reports matching the specified status(es) will be exported. Optional.
The following request parameters will be automatically processed:
-
requestJobDescription:
{{=Expensify.prepareReportRequest(parameters)}} -
template:
{{=parameters.template}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#report-exporter.
- [External]
-
The external fields URL address:
/. The external fields list is extracted from:{{=Expensify.getCSVFields(parameters, response)}}.- {{=external.name}}
Template: {{=external.template}}.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.policyList}}.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=Expensify.prepareReadJobRequest('policyList', parameters)}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#policy-list-getter.
-
requestJobDescription:
- [Read list] action
-
Endpoint URL address:
/.
The result is extracted from:{{=_.map(response.policyInfo, function(v, k) { return _.extend({ id: k }, v); })}}.The action has the following user-defined parameters:
- list: Required. Specify comma (,) separated list of identifiers.
The following request parameters will be automatically processed:
-
requestJobDescription:
{{=Expensify.prepareReadJobRequest( 'policy', _.extend({ policyIDList: parameters.list.split(','), fields: _.filter(parameters.fields, function(field) { return field.indexOf('.') == -1; }) }, parameters) )}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#policy-getter.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(Expensify.prepareWriteJobRequest( 'create', 'policy', { policyName: item.name, type: item.type } ))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#policy-getter.
-
requestJobDescription:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(_.extend( Expensify.prepareWriteJobRequest('update', 'policy', { policyID: item.id }), item ))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#policy-updater.
-
requestJobDescription:
- id
A key field. Template: ShortText.
- outputCurrency
Template: ShortText.
- owner
Template: ShortText.
- role
Template: ShortText.
- name
Template: ShortText.
- type
Template: ShortText.
- isPolicyExpenseChatEnabled
Data type: DT_BOOL
- inputSettings
Field components:
-
Uses template:
ShortText. - Contains the following components: type, policyName, plan, policyIDList.
-
Uses template:
- created
Template: DateTime.
- avatarURL
Template: ShortText.
- reportFields
Before setting, the value is processed with
{{={ action: 'replace', data: value } }}expression. Field components:- An array.
- Contains the following components: name, type, values.
- tags
Before setting, the value is processed with
{{={ data: value } }}expression. Field components:- An array.
- Contains the following components: name, setRequired, tags.
- tax
Field components:
-
Uses template:
ShortText. - Contains the following components: default, rates, name.
-
Uses template:
- employees
Field components:
- An array.
- Contains the following components: email, role, submitsTo, employeeID, customField2.
- categories
Before setting, the value is processed with
{{={ action: 'replace', data: value } }}expression. Field components:- An array.
- Contains the following components: name, enabled, payrollCode, glCode, commentHint, areCommentsRequired, maxExpenseAmount.
Based on resource template Base.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(Expensify.prepareWriteJobRequest('create', 'expenses', item))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#expense-creator.
-
requestJobDescription:
- employeeEmail
Template: ShortText.
- transactionList
Field components:
- An array.
- Contains the following components: created, currency, merchant, amount, reportID, policyID, externalID, category, billable, reimbursable, comment, tag, tax.
Based on resource template Base.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(Expensify.prepareWriteJobRequest('create', 'expenseRules', item))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#expense-rules-creator.
-
requestJobDescription:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(Expensify.prepareWriteJobRequest('update', 'expenseRules', item))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#expense-rules-updater.
-
requestJobDescription:
- policyID
Template: ShortText.
- employeeEmail
Template: ShortText.
- ruleID
Data type: DT_I4
- actions
Field components:
-
Uses template:
ShortText. - Contains the following components: tag, defaultBillable.
-
Uses template:
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(Expensify.prepareWriteJobRequest('update', 'reportStatus', item))}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#report-status-updater.
-
requestJobDescription:
- status
Template: ShortText.
- filters
Field components:
-
Uses template:
ShortText. - Contains the following components: reportIDList, startDate, endDate.
-
Uses template:
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.domainCardList}}.The action has the following user-defined parameters:
- domain: Required. Specify domain to get the cards for.
The following request parameters will be automatically processed:
-
requestJobDescription:
{{=Expensify.prepareReadJobRequest('domainCardList', parameters)}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/#domain-cards-getter.
- bank
Template: ShortText.
- cardID
A key field. Data type: DT_I8
- cardName
Template: ShortText.
- cardNumber
Template: ShortText.
- created
Template: DateTime. A read-only field.
Template: ShortText.
- externalEmployeeID
Data type: DT_I8
- lastImport
Template: DateTime. A read-only field.
- lastImportResult
Data type: DT_I4
- reimbursable
Data type: DT_BOOL
- scrapeMinDate
Template: ShortText.
Based on resource template Base.
- [Update] action
-
Endpoint URL address:
/.The action has the following user-defined parameters:
- dry-run: Required. Specify the desired history type on of email or response.
- onFinish: Optional. Specify in JSON format [{"actionName": "email", "recipients":"admin1@domain.com,admin2@domain.com"}].
The following request parameters will be automatically processed:
-
requestJobDescription:
{{=JSON.stringify(_.extend( Expensify.prepareWriteJobRequest('update', 'employees', { entity: 'generic' }), { dataSource: 'request' } ))}} -
data:
{{=JSON.stringify({ Employee: batch })}}
Documentation: https://integrations.expensify.com/Integration-Server/doc/employeeUpdater/.
- employeeEmail
Template: ShortText.
- managerEmail
Template: ShortText.
- employeeID
Template: ShortText.
- policyID
Template: ShortText.
- firstName
Template: ShortText.
- lastName
Template: ShortText.
- customField1
Template: ShortText.
- customField2
Template: ShortText.
- approvalLimit
Data type: DT_I4
- overLimitApprover
Template: ShortText.
- isTerminated
Data type: DT_BOOL
- domainGroupID
Template: ShortText.
- approvesTo
Template: ShortText.
- role
Template: ShortText.
- additionalPolicyIDs
Template: LongText. A composite field.
- [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 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
Knowledge Base
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.