API Profile

Description

Configure an API Profile as a source for data integration tasks to extract data from custom APIs using standard API profile definitions.

Task Type Support

This connection type supports the following task types:

  • Data Migration

Data Migration Support

This connection can be used in a Data Migration.

Usable as Source Usable as Target Usable as Incremental Source Usable as Incremental Target
✔ ✔ ✔ ❌

Connection String Templates

Template Name Description
Profile A connection string containing the path to the API Profile .apip file.
Profile with Profile Settings A connection string containing the path to the API Profile .apip file and additional profile configuration settings ProfileSettings=[Profile Configuration Settings].

Connection Notes

How to Connect an API Profile to Loome Integrate

This covers the core patterns and operational rules needed to successfully build, deploy, and troubleshoot API Profiles for Loome Integrate.

Overview

An API Profile allows Loome Integrate to consume REST APIs as relational tables without custom code.

The profile consists of:

  • RSD files: Define tables, endpoints, data mappings, transformations, paging, and query logic.
  • RSB files: Define authentication and reusable scripting components.
  • APIP file: Compiled deployment package used by the Loome API Profile connector.
  • Loome Connection Configuration: Connection string and profile settings used during execution.

Loome currently supports read operations only. Insert, Update, and Delete implementations are ignored except for table-classification stubs.

API Profile Developer Checklist:

  • One endpoint = one RSD table
  • Implement pagination wherever possible
  • Use query slicing for parent-child endpoints
  • Use other:xPath on every response field
  • Apply allownull() for sparse JSON fields
  • Stamp slicer values when formats may differ
  • Use columnsize="65535" for long text
  • Parse _query.criteria for incremental filtering
  • Enable Verbosity=5 during development
  • Build → Test in Loome → Review Logs → Repeat
  • Structure
    • One endpoint per RSD
    • Separate authentication into RSB files
    • Comment complex transformations
    • Use descriptive names
  • Performance
    • Always paginate
    • Use incremental loading
    • Minimise API calls
    • Prefer query slicing over manual nested loops
  • Security
    • Never hardcode credentials
    • Use ProfileSettings
    • Store OAuth tokens securely
    • Always use HTTPS

Steps:

Development Workflow

Step 1. Create a Profile

Using VS Code: 1. Run Create New API Profile task. 2. A profile structure is generated:

MyProfile/

│

├── src/

├── build/

├── debug/

└── README.md

Purpose of folders:

Folder Purpose
src RSD and RSB source files
build APIP output
debug Logs, cache, OAuth storage
README Profile documentation

Step 2. Build Tables with RSD Files:

Each API endpoint becomes an RSD file. An RSD contains:

Schema:

<api:info title="Users">
<attr name="Id" xs:type="integer" other:xPath="id"/>
<attr name="Name" xs:type="string" other:xPath="name"/>
</api:info>

Defines: - SQL table name - Columns - Data types - JSON extraction paths

Execution Logic:

<api:script method="GET">

<api:set attr="uri"
value="[_connection.BaseURL]/users"/>

<api:set attr="RepeatElement" value="/"/>

<api:call op="apisadoExecuteJSONGet">
<api:push/>
</api:call>

</api:script>

Defines: - Endpoint URL - HTTP method - JSON location - Returned rows

Core Design Principles:

Table Name

The exposed SQL table name comes from: <api:info title="Users"> and not from: users.rsd

Changing the file name does not rename the table.

One endpoint is equal to one table. Avoid combining unrelated endpoints in one file.

Recommended approach:

  • users.rsd
  • orders.rsd
  • products.rsd
  • locations.rsd

Data Mapping:

Basic Mapping:

JSON:
{
"id": 1,
"name": "ABC"
}

Mapping:

<attr name="Id" other:xPath="id"/>

<attr name="Name" other:xPath="name"/>

Nested JSON
JSON:
{
"contact": {
"email": "abc@test.com"
}
}

Mapping: <attr name="Email" other:xPath="contact/email"/>

Deep Nesting <attr name="Latitude" other:xPath="address/geo/lat"/>

Arrays “tags”: [“A”,“B”] <attr name="PrimaryTag" other:xPath="tags/0"/>

Value Transformations

You can make inline transformations.

Example: <api:set attr="Email" value="[Email | trim() | tolower()]"/>

Useful formatters:

Formatter Purpose
trim() Remove spaces
tolower() Lowercase
toupper() Uppercase
replace() Replace values
split() Extract pieces
substring() Partial text
def() Default value
capitalize() First letter uppercase
equals() Conditional comparison

For example: <api:set attr="Status" value="[Status | equals('Active','1','0')]"/>

Pagination

Always implement pagination when available.

Offset Paging

API pattern: ?limit=500&offset=1000

Configuration:

<api:set attr="EnablePaging" value="true"/>

<api:set attr="pageoffsetparam" value="offset"/>

<api:set attr="pagesizeparam" value="limit"/>

<api:set attr="pagesize" value="500"/>

Page Number Paging:

API pattern: ?page=2&limit=500

Configuration:

<api:set attr="EnablePaging" value="true"/>

<api:set attr="pagenumberparam" value="page"/>

<api:set attr="pagesizeparam" value="limit"/>

Incremental Loading:

Loome pushes SQL filters to the profile.

Example: WHERE ModifiedDate > '2025-01-01'

The profile should extract this filter using: [_query.criteria]

Then pass the value to the API endpoint.

Benefits:

  • Faster loads
  • Reduced API calls
  • Better performance for large datasets

Query slicing is the preferred approach when one endpoint depends on values from another.

Key Rules:

  • Use {ColumnName} inside URIs.
  • Use [ColumnName] elsewhere.
  • Every slicer column must have: other:filter="{ColumnName}"

Example:

Reservations

↓

ReservationDailyRates

The child table automatically loops through parent IDs.

Child Configuration:

<api:info
title="ReservationDailyRates"
other:queryslicercolumn="ReservationId"
other:slicetable="Reservations"
other:sliceforeignkey="Id">

Column: <attr name="ReservationId" other:filter="{ReservationId}"/>

URI: <api:set attr="uri" value=".../reservations/{ReservationId}/dailyRates"/>

Flattening Nested Arrays

When child arrays must become rows.

Example JSON:

{
"batch": {
"transactions": [...]
}
}

Configuration: <api:set attr="DataModel" value="FLATTENEDDOCUMENTS"/>

<api:set attr="JSONPath" value="$.value;$.value.Transactions"/>

Use absolute paths: other:xPath="/json/value/Transactions/Amount"

Never: other:xPath="Amount"

Authentication Options

  • API Key <api:set attr="http.header:Authorization" value="[_connection.APIKey | prefix('Bearer ')]"/>

  • Basic Authentication <api:set attr="auth.scheme" value="basic"/>

  • OAuth 2.0

    • Usually configured through the connection string rather than custom code.

Example:

AuthScheme=OAuth;
OAuthVersion=2.0;
OAuthGrantType=CLIENT;
OAuthClientId=...
OAuthClientSecret=...
OAuthTokenURL=...

Custom OAuth flows can be implemented in a separate: GetOAuthAccessToken.rsb

Building the Profile

You can then build the profile.

Run: Build API Profile: <Profile Name>

Output: build/MyProfile.apip

Connecting in Loome Integrate

In Loome Integrate, go to Connections at the top-right dropdown menu.

Create a new connection.

Choose ‘API Profile’ in the connector list.

Choose whether it will be available in all projects or only in your chosen projects.

Provide your connection string. In Loome, replace the profile in the connection string with the name of your own API profile and profile settings.

  • Basic connection: Profile=C:\Profiles\MyProfile.apip;

  • With settings: Profile=C:\Profiles\MyProfile.apip; ProfileSettings= 'BaseUrl=https://api.example.com; APIKey=xxxx';

Choose an agent and submit the connection.

Logfile=C:\debug\api-profile.log;
Verbosity=5;
Location=C:\debug;
CacheLocation=C:\debug;

Troubleshooting

Common Causes of Production Failures

These are the most common causes of production failures.

  1. Silent Row Loss During Slicing

    • Symptom:
      • API returns data
      • Logs show rows
      • Loome receives zero rows
    • Cause: Slicer value does not exactly match returned value.
    • Fix: Stamp the value manually.
      • <api:set attr="BranchID" value="[BranchID]"/>

  2. Column Shifting / Incorrect Data

    • Occurs when optional JSON fields appear in some rows and not others.
    • Always: <attr other:xPath="..."/> for response fields.
    • And apply: <api:set attr="SomeField" value="[SomeField | allownull()]"/> before: api:push/

  3. Long Text Truncation

    • Strings default to about 2000 characters.
    • For large text:
<attr
name="Description"
xs:type="string"
columnsize="65535"/>

  1. Table vs View

    • If only GET exists: <api:script method="GET">
    • It may be classified as a View. You can add unsupported POST/MERGE/DELETE stubs if it must appear as a Table.

  2. Expected API Errors

    • For slicer loops where some combinations legitimately fail: <api:catch code="*"/> inside: <api:call>
      • This avoids aborting the whole load.

Troubleshooting Checklist

You can check these fields when troubleshooting for these issues:

  • No rows returned, then check:
    • Endpoint URL
    • RepeatElement
    • JSONPath
    • Slicer placeholders
    • Stamp values
  • Wrong values in columns, then check:
    • Missing other:xPath
    • Sparse JSON
    • Missing allownull()
  • Authentication failures, then check:
    • Credentials
    • ProfileSettings
    • OAuth configuration
    • HTTP 401⁄403 in logs
  • Profile not found, then check:
    • APIP exists
    • Path is correct
    • Agent can access file