A consistent challenge for integration developers is managing flat file integrations with systems where direct access is not natively provided by existing functionality. In this guide, you'll see how to use Cloud Elements for bulk data download, then streaming the resulting .CSV file to a storage destination.
Overview
In this example, we'll use Cloud Elements to do the following:
- Create a bulk download of the objects from a resource (Salesforce Sales Cloud)
- Generate a CSV file from the object data
- Stream the file to a destination (Google Drive)
Although this tutorial specifically utilizes source Salesforce Sales Cloud (hereafter SFDC) contacts and streams to a Google Drive destination, both the source resources and endpoint, as well as the streaming destination, can be changed or extended to best suit your use case. For example, you could select top-level sources from nearly any bulk-supported endpoint and stream to any compatible destination, such as an FSFTP server, CRMs like HubSpot or Oracle Sales Cloud, or any API endpoint that can save files through a custom element. You could also utilize common resources to transform your Salesforce data into a format besides CSV, or add additional formulas to automatically sync corresponding data fields across platforms which otherwise couldn't integrate.
How It Works
This example uses two formulas to solve the use case:
Formula 1
Triggered according to your specified time and frequency, Formula 1 creates the bulk download job from your source resource, as well as the callback URL that will trigger Formula 2 and complete the workflow.
Formula 2
Triggered once the bulk job from Formula 1 is completed, Formula 2 generates metadata for the now-completed bulk job's CSV output, then streams that CSV to your destination.
Prerequisites
In order to follow along with and perform this use case yourself, you'll need the following:
- Cloud Elements account
- Salesforce Sales Cloud enterprise or professional account with API access and admin privileges (create an SFDC trial account here)
- Google Drive account (create a Google Drive account here)
- Authenticated instances of the SFDC and Google Drive elements (see our SFDC and Google Drive authentication docs)
- The following JSON files downloaded from our hub:
Performing the Use Case
Creating and Configuring the Formula Instances
With Cloud Elements, you can create formulas from scratch, use existing formulas as a starting point for new ones, or import JSON files of an existing formula. Here, we'll import the two JSON files you downloaded above as formulas, then configure the instances to work properly with each other.
Importing JSON to Create the Formulas
Your existing formulas are available from the Formulas tab on the left-hand side of the platform's interface. Click Build New Formula and then select Import to upload the JSON for each formula (see our docs for more details about importing).
After importing the JSON for the formulas, it's time to create the formula instances themselves.
Creating the Formula Instances
Because the kickoff of Formula 2 is triggered by (and therefore dependent on) a successful execution of Formula 1, we need to first create the Formula 2 instance. This will provide a required value for a parameter necessary to creating the Formula 1 instance.
From the Formulas catalog, hover over Formula 2, and click Create Instance.
On the Instance Creation page:
- Enter a name for the Formula 2 instance. Here, we use "Bulk step 2 instance".
- Click the plus sign under Target and select your Google Drive element instance.
- Click the plus sign under crmSource and select your Salesforce Sales Cloud element instance. In the object value field, enter "contacts".
It should look similarly to this:
After completing the configuration, click Create Instance.
Now, navigate back to Formula 2, hover over the formula card, and click the 1 to view the instance in the catalog. Note the ID of the instance (1465303 in the below example), which you will need to create the Formula 1 instance.
Return to the Formulas catalog, hover over Formula 1, and click Create Instance.
On the Instance Creation page:
- Enter a name for the Formula 1 instance. Here, we use "Bulk step 1 instance".
- Click the plus sign under CRM Source and select your Salesforce Sales Cloud element instance from the list.
- In the Cron field, enter `0 0 12 * * ?`. This is an arbitrarily chosen frequency at which to run the formula based on a more common use case; however, for the sake of this example, we will manually kick off the formula later in this article. After running the use case, remember to turn off the formula instance to keep it from repeating unnecessarily (at 12 PM every day, in this case). For more information about how to format cron strings, see our docs.
- In the Object field, enter "contacts".
- In the SecondFormulaInstanceId field, enter the ID for the Formula 2 instance you noted above.
It should look similarly to this:
After configuring, click Create Instance.
Triggering the Formulas
Now that both formula instances are created and configured, we’ll manually trigger the first formula (instead of waiting for the cron-defined 12 PM time to arrive).
Navigate to the Formulas catalog, hover over Formula 1, and then click Open.
On the Formula 1 canvas, click Try It Out, and then click Select Instance.
Select Bulk Step 1 instance.
Now click Run.
As the formula steps execute, they appear on the list.
With this formula successfully completed, we know that the bulk download job—above shown as “createBulkJob”, the last step in the formulas—has started. Once that completes, Formula 2 will begin.
Verifying the Formula Executions
After giving the formulas time to complete, we can view details of the formula executions to ensure they successfully completed.
Navigate to the Formula 1 instances page and click Executions.
In the Formula Executions column, click your Formula 1 execution to view its steps.
Here, we see all three steps of Formula 1—the trigger, the construction of the query, and the creation of the bulk job—were successful.
Do the same check on the Formula 2 execution to see the successful individual steps, as well. You can also click on an individual step to see more details.
After both formulas have successfully completed, the CSV file is now located in the Google Drive folder.
