ETL data from any source to API

Overview

In Etlworks, you can extract data from any source—such as a database, file, API, or SaaS app—and load it into a web service (HTTP API). Depending on how the destination API expects to receive data, you can use the following techniques:

1. Send all records as a JSON array (single HTTP call):

Use this method when both the source and the API payload are relatively flat, and the API expects a single HTTP call with a JSON array as the payload.

2. Send one record at a time using a tokenized payload:

Use this method when the destination API requires a more complex or nested payload. A template with {tokens} is used to build a custom JSON or XML document for each record.

3. Send one record at a time using a tokenized URL:

Use this method when the API expects parameters in the URL (query string or path), and you need to send requests to different endpoints or vary the parameters for each record.

These methods can be combined with nested flows and dynamic SQL to automate sending data in exactly the format the API expects.

Method 1: Send All Records as JSON Array (Single HTTP Call)

Use this method when both the source and the API payload are relatively flat, and the API expects a single HTTP call with a JSON array as the payload.

Step 1. Go to Connections and create a source connection.

Step 2. Create a destination HTTP API connection.

Step 3. If the API requires authentication, configure it under the Authentication section. For OAuth2, follow this guide.

Step 4. Go to Formats and create a JSON format with default settings.

Step 5. Go to Flows and create a new ...to Web Service flow, for example Database to Web Service

Step 6. Add a new transformation. Select:

• FROM: source connection and table

• TO: HTTP connection, JSON format, and API endpoint

Step 6. (Optional) Configure Source SQL under Mapping if needed.

Step 7. (Optional) Adjust field mapping.

Step 8. Test the transformation. The expected output is a JSON array. Example:

[
   { "id": 1, "first_name": "Joe", "last_name": "Doe", "dob": "01/01/2000" },
   { "id": 2, "first_name": "Jane", "last_name": "Doe", "dob": "01/01/2001" }
]

Step 9. If further transformation is needed (e.g., nested JSON) use any of these techniques.

Method 2: Send One Record at a Time Using Tokenized Payload

Use this method when the destination API requires a more complex or nested payload. A template with {tokens} is used to build a custom JSON or XML document for each record.

Step 1. Go to Connections and create a source database connection.

Step 2. Create a destination HTTP API connection.

Step 3. If the API requires authentication, configure it under the Authentication section. For OAuth2, follow this guide.

Step 4. Create a Flow: Call HTTP Endpoint.

Step 5. Use the HTTP connection as the Flow connection.

Step 6. Use the HTTP connection as the Flow connection.

Step 7. In the Parameters tab, enter the payload using {tokens}. Example:

{
   "source": "test",
   "userId": {id},
   "firstName": {first_name},
   "lastName": {last_name},
   "dateOfBirth": {dob}
}

Step 7. Create a nested flow.

Step 8. Add the HTTP call flow created in Steps 4-6 to the nested flow and click Configure condition and loop.

Step 9. Set Loop Type to SQL, select the source connection, and enter the driving SQL.

How does token replacement in template work

When main nested flow runs it will execute a Loop SQL, create parameters for each record returned by SQL and replace values of the {tokens} in the template sent in the HTTP call flow created in Steps 4-6.

Method 3: Send One Record at a Time Using Tokenized URL

Use this method when the API expects parameters in the URL (query string or path), and you need to send requests to different endpoints or vary the parameters for each record.

The steps are almost identical to the previous method.

When creating a destination HTTP connection and use {tokens} in the URL:

https://myapi.com/?id={id}&firstName={first_name}&lastName={last_name}&dob={dob}

How does token replacement in URL work

When main nested flow runs it will execute a Loop SQL, create parameters for each record returned by SQL and replace values of the {tokens} in the URL.

Next Steps

• Want to extract records from an API that returns paginated results? Learn about Pagination in HTTP Connector.

• Working with nested API responses? See Working with Nested Documents and Formats.