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

This tutorial extracts data from an HTTP API endpoint, optionally transforms it, and loads it into any destination — typically a relational database. Etlworks handles paginated and nested responses, flattens them, and maps and merges the data into destination tables.

How do I set this up?

1. In Connections, create a source HTTP API connection.

   

2. If the API requires authentication, configure it in the Authentication section. For OAuth2, see Authentication Methods for HTTP API Connector — OAuth2.
3. If the API returns a nested response (metadata wrapper around the data), configure Data JSON Path or Data XPath on the connection's Output and Formatting section.

   For example, given:

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

   set Data XPath to:

   /data/items

4. If the response is nested and you need it as a single flat database table, use the built-in flatten options on the HTTP connection:
   • Enable Output as CSV to convert the response into CSV.
   • Enable Denormalize Nested Fields to flatten nested fields into columns.

   Useful when the response is a JSON array of nested objects and the destination is a database or a CSV file. See Another Way to Work with Nested API Responses.

   For other flattening options — Source SQL, nested mapping, format-level transformations — see Working with Nested Documents and Formats.

5. If the API supports pagination, configure the Pagination section on the HTTP connection. Etlworks supports offset, page, cursor, nextLink, and time-based pagination. See Work with Paginated APIs Using the HTTP Connector.
6. In Formats, create a format for the API response — usually CSV, JSON, or XML.

   

7. Test the API response in Explorer:
   • Open Explorer, select the source connection, and link it to the format from step 6.
   • Run the endpoint to verify the response.
   • If the response is nested or paginated, confirm the format flattens it correctly.

   Note: If you enabled Output as CSV, pick the CSV format; otherwise pick JSON or XML to match the response.

   

8. Create the destination connection — for example, a relational database — and test it.
9. In Flows, create a new Web Service to… flow — for example, Web Service to Database.

   

10. Add a transformation:
    • FROM — the HTTP connection, format, and API endpoint (URI path).
    • TO — the destination connection and table.

    Note: If you enabled Output as CSV, pick the CSV format; otherwise pick JSON or XML.

    

11. Optional: click Configure:
    • Add a Source Query if you need to transform the response.
    • Configure per-field mapping.

    

12. Test the transformation.

    

13. Optional: enable MERGE (UPSERT) to update existing records.
14. Save the flow and execute it manually.
15. Schedule the flow: go to Schedules and create a schedule to run it periodically.