Updated (5/7/2019). Etlworks now includes a native connector for Salesforce but this article is still relevant if you need to access various Salesforce APIs (for example a streaming REST API) not supported by the native connector.
In Etlworks, it is possible to connect to practically any HTTP-based API -the Salesforce API is no exception. This blog post provides step-by-step instruction for creating a connection to the Salesforce REST API in Etlworks.
Creating Connected App in Salesforce.
Assuming that you already have a Salesforce account, the first step is to create a connected app in Salesforce.
Step 1. Login into Salesforce and click the Setup icon (looks like gears) in the top navigation banner.
Step 2. Search for “apps” and select the “Apps/App Manager” link in the left side-bar.
Step 3. Click the “New Connected App” button in the top right corner.
Step 4. Enter all required parameters and check Enable OAuth settings.
Step 5. In the “Selected OAuth Scopes” settings, add the Full Access (full) scope.
Step 6. Enter the following URL in the “Callback URL” field:
https://app.etlworks.com/salesforce/callback
Step 7. Click the “Save” button and continue to the next screen (there will be a message saying that you need to wait for a few minutes before you can start using the app).
Step 8. In the next screen, under the “API (Enable OAuth Settings)” there will be a “Consumer Key” and a “Consumer Secret”. Copy and save them somewhere, we will need them later when you create a connection to the Salesforce API.
Creating a connection to Salesforce API
In this section, we will be showing you how to connect to the Query API, which takes a SQL-like query as an URL parameter and returns a JSON for the requested object.
Step 1. Add a new HTTP connection in Etlworks.
Step 2. Enter the following URL in the “URL” field.
https://your_salesforce_instance.lightning.force.com/services/data/v20.0/query?q=SELECT+name+,+email+,+Username+,+LastName+,+FirstName+,+IsActive+,UserRole.Name+,+Id+from+User
As you can see, we are using a query API to get all the users under your Salesforce account, together with roles.
Step 3. Select GET as a “Method” and application/x-www-form-urlencoded as a “Content Type Header”.
Step 4. Select oauth2 as an “Authentication”. Enter URL encoded username in the “User or Access Key” field and the password in the “Password” field. For example, if the username is first.last@company.com, the encoded URL is going to be first.last%40company.com.
Step 5. Enter the following string in the “Authentication URL” field:
https://login.salesforce.com/services/oauth2/token
Select POST in the “HTTP Method for Token and OAuth2 Authentication”.
Step 6. Enter the following string in the “Authentication Request Payload” field:
client_id=client_id&client_secret=client_secret&grant_type=password&username={user}&password={password}
where the client_id is the “Consumer Key” and the client_secret is the “Consumer Secret” from step 8 in the “Creating Connected App in Salesforce” section.
Step 7. Select application/x-www-form-urlencoded as an “Authentication Request Content Type”.
Step 8. Give the connection a name and save it.
Testing Salesforce connection
The following shows you how to test the connection to the Salesforce API using Explorer.
Step 1. If you don’t have it already create the JSON format. Use all the default settings.
Step 2. Go to Explorer, select the connection created in the previous section and click “expand” (down-arrow) button. Link the format created in step 1 to the Salesforce connection.
Step 3. Click the “View Data” button to view the data in a grid format or “Raw Data” to view the data in the JSON format.
Etlworks Blog 