Overview
Configuration is provided for establishing connections with QuickBase 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 the service resource.
Insert data
Use REST Destination component to insert data into service resource.
Demonstration
Quick Start
In this task, we will show you step-by-step how to create a connection to QuickBase REST API using COZYROC's REST Connection Manager. Please review the QuickBase documentation page here. You will need the following information:
- Realm - your Quick Base domain.
- Token - follow the QuickBase documentation on how to generate a user token. Prefix permanent token with
QB-USER-TOKEN {token}. Prefix temporary token withQB-TEMP-TOKEN {token}. - Application - QuickBase application identifier. One way to extract the identifier is by opening the QuickBase application page in a browser. The identifier will be displayed in the navigation link.
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 QuickBase. Enter the required parameters in the places shown in the screenshot.
Step 3. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your QuickBase instance.
In this guide, we will show how to read data from the QuickBase 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 QuickBase connection from the combo. Select from available QuickBase 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 QuickBase 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 QuickBase service.
In this guide, we will show how to write data to QuickBase 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 QuickBase connection from the combo. Also, select resource action and QuickBase 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 QuickBase resource.
Configuration
Base URL address: https://api.quickbase.com/v1.
- Token
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- Realm: Required. Specify QuickBase domain.
- Token: Required. Specify permanent or temporary token. Permanent prefix: QB-USER-TOKEN, Temporary prefix: QB-TEMP-TOKEN
- Application: Required. Specify application identifier.
The following request parameters will be automatically processed during the authentication process:
-
QB-Realm-Hostname:
{{=connection.user.Realm}}.quickbase.com -
Authorization:
{{=connection.user.Token}} -
appId:
{{=connection.user.Application}}
Documentation: https://developer.quickbase.com/auth.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/tables.Documentation: https://developer.quickbase.com/operation/getAppTables.
- [Create] action
-
Endpoint URL address:
/tables.Documentation: https://developer.quickbase.com/operation/createTable.
- [Update] action
-
Endpoint URL address:
/tables/{{=item.id}}.Documentation: https://developer.quickbase.com/operation/updateTable.
- [Delete] action
-
Endpoint URL address:
/tables/{{=item.id}}.Documentation: https://developer.quickbase.com/operation/deleteTable.
- id
A key field. Template: ShortText.
- name
Template: ShortText.
- created
Template: UnixTime. A read-only field.
- updated
Template: UnixTime. A read-only field.
- alias
Template: ShortText.
- description
Template: ShortText.
- nextRecordId
Data type: DT_I4 A read-only field.
- nextFieldId
Data type: DT_I4 A read-only field.
- defaultSortFieldId
Data type: DT_I4
- defaultSortOrder
Template: ShortText.
- keyFieldId
Data type: DT_I4 A read-only field.
- singleRecordName
Template: ShortText.
- pluralRecordName
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/fields.The action has the following user-defined parameters:
- tableId: Required. Specify table identifier.
Documentation: https://developer.quickbase.com/operation/getFields.
- [Create] action
-
Endpoint URL address:
/fields.The following request parameters will be automatically processed:
-
tableId:
{{=item.tableId}}
Documentation: https://developer.quickbase.com/operation/createField.
-
tableId:
- [Update] action
-
Endpoint URL address:
/fields/{{=item.id}}.The following request parameters will be automatically processed:
-
tableId:
{{=item.tableId}}
Documentation: https://developer.quickbase.com/operation/updateField.
-
tableId:
- [Delete] action
-
Endpoint URL address:
/fields.The following request parameters will be automatically processed:
-
tableId:
{{=item.tableId}} -
application/json:
{{={ fieldIds: [item.id] } }}
Documentation: https://developer.quickbase.com/operation/deleteFields.
-
tableId:
- id
A key field. Data type: DT_I4
- fieldType
Template: ShortText.
- mode
Template: ShortText.
- label
Template: ShortText.
- noWrap
Data type: DT_BOOL
- bold
Data type: DT_BOOL
- required
Data type: DT_BOOL
- appearsByDefault
Data type: DT_BOOL
- findEnabled
Data type: DT_BOOL
- unique
Data type: DT_BOOL
- doesDataCopy
Data type: DT_BOOL
- fieldHelp
Template: ShortText.
- audited
Data type: DT_BOOL
- properties
Field components:
-
Uses template:
ShortText. - Contains the following components: primaryKey, foreignKey, allowNewChoices, sortAsGiven, carryChoices, numLines, maxLength, appendOnly, allowHTML, formula, defaultValue.
-
Uses template:
- permissions
Field components:
- An array.
- Contains the following components: roleId, role, permissionType.
- tableId
A key field. Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/reports.The action has the following user-defined parameters:
- tableId: Required. Specify table identifier.
Documentation: https://developer.quickbase.com/operation/getTableReports.
- id
A key field. Template: ShortText.
- name
Template: ShortText.
- type
Template: ShortText.
- description
Template: ShortText.
- query
Field components:
-
Uses template:
ShortText. - Contains the following components: tableId, filter, formulaFields, fields, sortBy, groupBy.
-
Uses template:
- properties
Template: LongText. A composite field.
- [Read] action
-
Endpoint URL address:
/records/query.
The result is extracted from:{{=QuickBase.getData(response)}}.
The action uses POST method.The action has the following user-defined parameters:
- where: Specifies the filter, using the Quick Base query language.
- sortBy: Specified sort criteria.
The following request parameters will be automatically processed:
-
application/json:
{{={ from: resource.external.id, select: function() { var fields = resource.getFields(); return _.map( _.filter(parameters.fields || [], function(field) { return field.indexOf('.') == -1 }), function(field) { return fields[field.toLowerCase()].external.id; }); }(), where: parameters.get('where'), sortBy: parameters.get('sortBy'), options: { skip: parameters.iterator, top: parameters.batchSize } } }}
Documentation: https://developer.quickbase.com/operation/runQuery.
- [Read report] action
-
Endpoint URL address:
/reports/{{=parameters.reportId}}/run.
The result is extracted from:{{=QuickBase.getData(response)}}.
The action uses POST method.The action has the following user-defined parameters:
- reportId: Required. Specify report identifier.
The following request parameters will be automatically processed:
-
tableId:
{{=resource.external.id}} -
skip:
{{=parameters.iterator}} -
top:
{{=parameters.batchSize}}
Documentation: https://developer.quickbase.com/operation/runReport.
- [Upsert] action
-
Endpoint URL address:
/records.
The result is extracted from:{{=function() { var result = _.map( response.data || [], function(item) { var status = { code: 0 }; status[resource.keyField] = item[3].value; return status; } ); if (response.metadata && response.metadata.lineErrors) { _.each(response.metadata.lineErrors, function(v, k) { result.splice(parseInt(k) - 1, 0, { code: 1, message: v.join('\n') }); }); } return result; }()}}.
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{={ to: resource.external.id, data: function() { var nameToId = {}; _.each(resource.getFields(), function(field) { if (field.external) nameToId[field.name] = field.external.id; }); return _.map(batch, function(item) { var record = {}; _.each(item, function(v, k) { record[nameToId[k]] = { value: v }; }); return record; }); }(), mergeFieldId: parseInt(parameters.get('mergeFieldId')) || undefined, fieldsToReturn: [3] } }}
Documentation: https://developer.quickbase.com/operation/upsert.
-
application/json:
- [Delete] action
-
Endpoint URL address:
/records.The following request parameters will be automatically processed:
-
application/json:
{{={ from: resource.external.id, where: '{3.EX.' + item[resource.keyField] + '}' } }}
Documentation: https://developer.quickbase.com/operation/deleteRecords.
-
application/json:
- [External]
-
The external fields URL address:
/fields.- {{=external.label}}
Template: {{=QuickBase.getFieldTemplate(external.fieldType)}}. Data type: {{=QuickBase.getDataType(external.fieldType)}} ( length {{=external.properties.maxLength || 255}} )
The external resource list URL address: /tables.
Based on resource template Base.
- [Read] action
-
The result is extracted from:{{=response}}.
The result is paginated.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
-
_includeUserParameters:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Update] action
-
The action uses POST 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
- User
Field components:
-
Uses template:
ShortText. - Contains the following components: id, email.
-
Uses template:
- UnixTime
Data type: DT_DBTIMESTAMP The gathered value is processed with
{{=value && new Date(value)}}expression.
What's New
- Fixed: Unable to process fields containing dot (.) symbol (Thank you, Dale).
- Fixed: Infinite loop when trying to retrieve data from 'System | Table' resource (Thank you, Dean).
- Fixed: Modified to use Windows-1252 encoding for handling data returned by the service (Thank you, Dean).
- 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.