How to extract data from an API and load it into any destination

Overview

This flow extracts data from an API endpoint, optionally transforms it, and loads it into any destination, for example a relational database. Etlworks supports handling paginated and nested responses, allowing you to flatten, map, and merge the data into destination tables.

Step-by-Step Instructions

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

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

Step 3: If the API response is nested (e.g., contains metadata and a data node), configure the Data JSON Path or Data XPath in the connection under Output and Formatting section.

Example:

For the response like this:

{
   "data": {
      "items": [ {...}, {...} ]
   }
}

Set Data XPath to 

/data/items

Step 4. If the API response is nested and you need to load it into a single flat table in the database, you can use the built-in Output and Formatting options in the HTTP connection:

• Enable Output as CSV to flatten the response and convert it into CSV format.

• Enable Denormalize Nested Fields to extract nested fields into flat columns.

These settings are helpful when the response is a JSON array of nested objects and you want to work with a flat tabular structure (e.g., for databases or CSV export).

Learn more → Another Way to Work with Nested API Responses

Learn other methods for flattening or normalizing the nested dataset using Source SQL, nested mapping, or format-level transformations → Working with Nested Documents and Formats

Step 5: If API supports pagination, also configure the Pagination section in the HTTP connection. Etlworks supports offset, page, cursor, nextLink and time-based pagination.

Learn more → Work with Paginated APIs

Step 6. Go to Formats and create a format for the API response. Most commonly this will be CSV, JSON or XML.

Step 7. Test the API response using the Explorer

• Go to Explorer, select the source Connection and link it to the Format created in Step 5. 

• Test the endpoint to ensure a valid response.

• If the response is nested or paginated, ensure the format flattens it correctly.

Note: if you enabled Output as CSV select CSV format, otherwise JSON or XML, depending on the format of the response. 

Step 8. Create a connection for the destination, for example relational database. Test the connection.

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

Step 10. Add a new transformation.

• Select the FROM Connection, Format, and API endpoint (URI path). 

• Select the TO Connection and destination table.

Note: if you enabled Output as CSV select CSV format, otherwise  select JSON or XML, depending on the format of the response. 

Step 11. (Optional): Click Configure.

• Configure a Source Query if you need to transform the nested response further.

• You can also configure per-field mapping.

Step 12. Test the transformation. 

Step 13. (Optional) Enable MERGE (UPSERT) if you want to update existing records.

Step 14. Save the Flow and execute it manually.

Step 15. Schedule the flow

• Go to Schedules

• Create a schedule to run the flow periodically