Working with web services

In Integrator, it is possible to extract data from and push data into practicably any REST-based or SOAP-based web service. Integrator also includes a growing number of built-in connectors to the most commonly used APIs.

Connecting to web services

Using an HTTP connector

Create an HTTP connection to connect to a web service.

Authentication

HTTP connector supports the following types of authentication:

Read how to configure authentication for a web service.

Formats

Web services use different data exchange formats, which must be described in Integrator:

Parametrization and Dynamic URLs

Read about parametrization.

Read about dynamic URLs.

Testing and exploring API endpoints

Use the Explorer to test the connection to a web service and explore API endpoints.

Step 1. Create an HTTP connection to the web service.

Step 2. Create a format for the response. The most commonly used formats are JSON, XML and CSV.

Step 3. Open Explorer, select the connection created in Step 1 and link it to the format created in Step 2. Read more about how to link the format to a connection in Explorer.

Step 4. Explore the metadata (endpoints and fields), view data in a grid, query data and discover dependencies using SQL.

The API endpoint is a source

Integrator can extract data from web services, transform it and load it into any destination, such as a database, files, a third-party system via the API, etc.

Read how to configure transformations when the source is an API endpoint.

Extracting data from complex nested datasets

Read more about how to extract data from a nested source data object using SQL and, specifically, writing queries.

Also, read how to extract data from a nested source data object using built-in transformations. Note: there are multiple options, so please read all the paragraphs on this linked page.

The API endpoint is a destination

Integrator can extract data from files, API endpoints, social web sites, databases, etc., transform it and load it into a third-party system via an API endpoint .

Read how to configure a transformation when the destination is an API endpoint.

Creating complex nested payloads

Read how to create a complex nested payload when the destination is an API endpoint.

If the payload is XML, read how to use an XST transformation to create complex XML.

Read how to use JavaScript to create complex nested payload.

Using a template to populate columns in the request

In Integrator, it is possible to use a template to define fields for the request. You can then use a visual designer to map the source columns to the request (payload) columns.

Read how to use a template for the request.

Creating a payload using a template with tokens

Read how to create a payload using a template with tokens.

Testing a payload

Read how to test a payload without actually calling a web service.

Other use cases

Debugging an HTTP request

Below are the typical issues (together with the solution), one faces when submitting requests to HTTP-based web services in Etlworks Integrator:

No detailed response in the log

If the HTTP request is executed with an error, but only the HTTP error code is displayed in the logs.

Solution: Add a more detailed message to the log. When configuring the HTTP connection, select the option Add Response to Exception Log.

add an HTTP response to log

Impossible to guess the URL

If a dynamic URL is used, it is impossible to figure out the actual URL for the request.

Solution: To add an actual URL to the log, when configuring the HTTP connection, select the option Add URL to Debug Log.

add URL to debug log

View debug log from the Flow Builder

View debug log from the Audit-Trail Grid

An HTTP Error code is in the response when it is actually a success

The web service returns an HTTP code that is, according to the standard, an error code for that specific HTTP method. However, it is actually a success for our web service.

Solution: Set exceptions from the standard HTTP error codes, when configuring the HTTP connection. Enter a comma-separated list of the exceptions in the Exceptions from Error Codes field. When an HTTP request is executed, the return code will be compared to the list of standard error codes, and the list of the exceptions. The error will be generated only if the code is not on either list.

http exceptions

An HTTP Success code is in the response but it is actually an error

The web service returns an HTTP code that is, according to the standard, a success code for the specific HTTP method. However, it is actually an error for our web service.

Solution: To set the HTTP codes that will be considered to be a success, when configuring the HTTP connection, enter a comma-separated list of the codes in the Success Codes field. When a HTTP request is executed, the return code is compared to the list of standard success codes, and the list of user-entered success codes. If the second list is not empty, only the codes from the second list will be considered a success.

http success codes

Save the HTTP response to a file

Quite often, we need to save the content of a HTTP response to a file, for example, to parse it later.

Solution: To save a response to a file, when configuring the HTTP connection, enter the name of the file in the Response File Name field. Example: {app.data}/response.json. The actual file will be created in the home folder, and the name of the file will be response_uuid.json. You can use token {app.data} as a part of the response file name. {app.data} is the home folder.

http response file name

You can also use the token {destination} as part of the Response File Name. The {destination} token will be automatically replaced for the value provided in the TO field of the file management operation or source to destination transformation.

For example:

Response File Name - {app.data}/response/{destination}

TO - replace(_processing.xml, _response.json)

Source file - order_processing.xml

The response file will be created in the home folder and will have the name order_response.json.

Note: you can configure an HTTP connection to create a response file only if the HTTP request was executed with an error. Just enable the option Save Response only on Error.

Copy the response from an HTTP request to a file

In Integrator, it is possible to copy an unmodified response from the HTTP request to a file.

Please note that this is not the same as saving an HTTP response to a file.

Sending a payload to an HTTP endpoint

If there is no source-to-destination transformation, and you just want to send a payload to the HTTP endpoint, use the flow type Send payload to HTTP connection.

Read how to send a payload to an HTTP endpoint.

Using the content of a file as the payload for an HTTP endpoint

Read how to use the content of a file as a payload for an HTTP endpoint.

Exposing any dataset as an API endpoint

Read how to create an endpoint in Integrator, which exposes any dataset as a web service.

Sending payload to API endpoint in a loop

Step 1. Create an HTTP connection with a tokenized payload.

Step 2. Create a flow which uses HTTP connection created in Step 1 to send a payload to the API endpoint.

Step 3. Create a nested flow and add the flow created in Step 2.

Step 4. Configure it to run a flow in a loop.

Step 5. For a loop in SQL, use the same field names in the select statement as the token names in the payload.

For example (payload):

{
  "Device": "{name}",   
  "HostName": "{host}",
  "DomainName": "{domain}",
  "FullyQualifiedDomainName": "{domain_full}",
  "Description": "{description}"
}  

For example (loop SQL):

select name, host, domain, domain_full, description from staging_table

Working with paginated APIs.

Read how to work with paginated APIs in Etlworks Integrator.

Loading data from a web service into Snowflake

Use the flow type "Extract data from Web service, load into Snowflake".

web service to Snowflake

Read how to configure a Snowflake flow.

Using an HTTP listener to upload files

It is possible to use an HTTP listener to upload files into all of the supported file storage types.

Step 1 Create an HTTP Listener.

Step 2 When creating a Listener:

file receiver

Step 3 Create a Copy Files flow.

Step 4 Set the source (FROM) connection to the Listener created in Step 1.

Step 5 Set the destination (TO) connection to any "file" connection (Server Storage, FTP, FTPS, SFTP, Amazon S3, HTTP, etc.).

Step 6 Set TO to either an actual file name, for example test.json, or a token in the URL, which was created in Step 2, for example {filename}.

receive file

Step 7 Schedule the event-driven flow, created in Step 3.

Testing a file upload

To test file upload you can use Postman.

Step 1. Add a new tab.

Step 2. Set the method to POST.

Step 3. Set the URL to the URL you configured when creating the Listener. For example, if the URL is /file/{filename}, the URL in Postman is going to be https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/file/test2.json, where test2.json is a file name (you can use any file name).

Step 4. Set the Authorization to Basic (if that is what you used previously, when creating the Listener).

Step 5. Enter the username and password of any valid user under your Etlworks account.

Postman sending file

Step 6. Select Body, click Raw, select Text, and enter the content of the file (the payload):

file payload

Step 7. Click Send and check the response. Make sure that the file has been created.

Integrations and APIs

Read about Integrator's APIs.

Making asynchronous HTTP calls

Enable asynchronous HTTP calls to POST, PUT, and DELETE endpoints by setting the property Maximum Asynchronous HTTP Calls to a value greater than zero (0 is the default).

Asynchronous HTTP

Built-in connectors

Google Analytics

Read how to connect to Google Analytics in Integrator.

Google Sheets

Read how to connect to Google Sheets in Integrator.

Amazon MWS

Read how to work with the Amazon MWS API in Integrator.

Social Networks

Read how to connect to social networks, such as Twitter and Facebook.

Tips and tricks

Tips and tricks when working with web services

Read about tips and tricks when working with web services.

Common tips and tricks when working with data

Read about common tips and tricks when working with data.

Performance tips

Read about common performance tips.