Skip to main content

Your first integration

This walk-through takes a cloud REST API from nothing to a scheduled load into SQL Server. It uses only the UI. Each step links to the page that covers it in full.

:::tip Business Central or SharePoint?

Install the Business Central (BI Endpoints) or SharePoint Online — files template from Templates instead of steps 1 and 2. It creates the source system and instance with sign-in already set up. Then continue at step 3, where the Data catalogue lists entity sets or files instead of endpoints. See Templates.

:::

Before you start​

1. Create a source system​

  1. Open Source Systems and press New Source System.
  2. Fill in:
    • Source System — the name, for example Webshop.
    • Schema — where the data lands, for example webshop. The tables will be [webshop].[<dataset>].
    • System kind — Cloud (HTTPS API).
    • Response format — JSON (default).
  3. Save. A new source system starts disabled: switch it on with the Status switch on the list page.

See Source systems.

2. Add an instance with credentials​

An instance is one connection: a base URL and credentials. Several instances can load the same datasets from several companies or tenants.

  1. Click the source system's row to expand it, and in Instances press Add Instance.
  2. Enter an Instance Name, the Base URL (for example https://api.example.com/v1) and the Authentication Type. For OAuth 2.0 client credentials, pick Session Bearer.
  3. Press Create. The dialog stays open with the tabs Edit instance, Credentials and Configurations.
  4. On Credentials, press Setup Credentials and fill in the form. Put the client secret in an encrypted variable and reference it as {{webshopSecret}}: values typed straight into a credential are stored as typed. Typing {{ opens the variable picker; an orange chip means the variable does not exist yet, and clicking it creates it.
  5. Switch the instance on with its Status switch.

See Instances and credentials and Variables.

3. Add an endpoint and preview it​

  1. In the expanded source system, open Data catalogue and press Add Endpoint.

  2. Choose Method GET, Load Type Full, and the URL path, for example /orders. Save.

    To add many at once, press Import endpoints instead and import a Postman collection or, on Standard and Enterprise, let the AI read the API's documentation.

  3. Press Preview on the endpoint. Cloud Connector makes one live request and shows the response as a tree.

  4. Click the target icon next to the array that holds the records to set the unpack path, then click the field (or fields) that identify a record to set the business key.

See Endpoints and pagination. If the API pages its results, set Pagination (NextLink) on the endpoint or the instance.

4. Create the dataset and load it​

  1. In the preview, press Create dataset. The Assign 1 endpoint to dataset dialog proposes a Dataset name from the path and fills in the Business key and Unpack you picked.
  2. Create the dataset with Run now, which queues its first full load straight away.
  3. Open Datasets in the sidebar. The row shows the Status of the last load, Last Loaded At and Records. View log shows the endpoint URLs that were called; Preview loaded data shows the first 200 rows.

See Data catalogue and Datasets.

5. Add an incremental endpoint​

A full load reads everything every time. If the API can filter on a modification time, add an incremental sibling:

  1. In the dataset's Endpoints section, press Copy on the full endpoint. The copy defaults to Incremental.
  2. Put the filter in the URL path, for example /orders?modifiedSince={{system:lastLoadedAt}}. {{system:lastLoadedAt}} is the start of this instance's previous finished load, and is resolved in the URL path only.

An incremental refresh now reads only what changed. See How a load works and System variables.

6. Export to SQL Server​

  1. Open Export Systems and create an MS SQL Server export system with the server, database and credentials (again, reference a variable for the password). Prepare the database as the dialog describes and press Test Connection.
  2. Edit the source system, open the Export tab, and add the export system under Export to (optional).

From the next load on, every dataset of the source system is written to [webshop].[<dataset>] after each successful load. See Export systems.

7. Put it on a schedule​

  1. Open Jobs and create a job. In Refresh Source Data, press Add dataset refresh and add an item for the source system, all instances, all datasets, Incremental.
  2. Optionally add an action to After refresh data, for example a Power BI refresh.
  3. At the end of the job form, Next: Schedule offers Create new schedule with this job. Add a When to run line, for example daily at 06:00, and save.

The schedule now runs the job every morning. Failed runs email the Failure notification recipients from Setup → Runtime. See Jobs, Schedules and Notifications.

Where to go next​

  • Load on-premises databases with the Gateway.
  • Let Power BI read the data directly through the Data API.
  • Start loads from other systems with trigger URLs.