Database storage is important for archiving and reporting. The source of the reporting data can be from many systems in an organisation. The issue is generally moving data from these various systems to a reporting database easily and reliably. Synatic makes it simple to transfer your data no matter your database and software systems.
In this tutorial, you will learn how to move data from Salesforce to a SQL database.
To start, log into your Synatic account. If you haven't already got one, read how to setup an account here.
Make a new Solution
Create a new solution to hold your flow by clicking the green + next to the Solutions section on the side bar, and give it a name and description. Then click Save.
Make a new Flow
Create a new flow in your solution by clicking the > next to your new solution in the side bar to expand it. Then click the green + next to the Flows item to add a new flow. Give the flow a name and description, and click save.
Add a Salesforce source
The Salesforce source will access your Salesforce account with a security token and extract data via a SOQL query.
Click on the Show Steps menu item to show the steps palette. Search for Salesforce and drag and drop the Salesforce source step as shown. Note that it must be dropped between the Parameters and Return blocks. Then click Save on the step dialog.
How to link Salesforce to Synatic
Now that we have added our source, you will need to sign into Salesforce to get a security token. Click the + next to connection. Fill in the popup form, and click "logon to Salesforce" under the oAuth block. Click "Login to salesforce" on the right hand bar. A new window will open where you can grant access to Synatic from your Salesforce account.
Once you receive the notification that this has been completed click “Ok”. You have successfully linked Salesforce to Synatic. Then click Save on the step dialog.
Enter SOQL query to extract information from Salesforce
Now that you have linked the two systems, you can add a query. Under the command field add your SOQL query. For this example, we are going to use the following query to extract information from the accounts object:
SELECT Id,Name,Description,AccountNumber FROM Account
After adding the query, click Save Step.
Now that we've got a source of data, we can test that it works to this point. To test, click on the blue play button just after the Pass-through step. Then click Test on the dialog box that comes up. You should see a list of records show in the debugger from the accounts object.
Mapping fields from Salesforce to SQL
From the debugger you can see we have records to pass through to the database. By adding a Field Mapper step we can match the fields extracted from Salesforce to column names in the SQL database.
For the above example, we have matched Id and Name to the field within our SQL DB. Open the steps palette, and search for the the Field Mapper step. Drag the step onto the flow, just after the Pass Through step. Once you have inserted the mapper step, fill in the Mappings table, by filling in the From Path and To Path columns.
In this example, the field names in Salesforce are Id and Name. Fill these values in the From Path column. The corresponding columns in our SQL database are ID and Name. Add these values to the To Path column
Once you have added the fields to map, click Save Step. Then click on the play button to the right of the Field Mapper step to confirm that your fields have mapped correctly.
Select the destination (SQL)
Now we select the destination where we would like to insert the data. Open the steps palette, and click on search bar on the left hand side of the canvas. Search for SQL. Select the SQL Server step under the Destinations draw, and drag and drop this step at the end of the flow, just after the Field Mapper step.
A configuration dialog will popup. Under SQL Server Connection, click + to add a new connection. You can also select and existing connection from the dropdown list. See here on how to setup a connection.
Once you have selected the connection, you will need to select the object name (Table) you are going to insert the data into. Then you will need to click on the + button at the bottom by Column mapping to map the fields and data types you would like to insert into the DB. You will need to populate the Path, Column and Data type entries.
Running the Flow
The flow is complete, so let's give it a run. Click on Execute Flow above the canvas. Then click Create Run in the dialog box that pops up.
After a short time, you should see Completed with a green circle next to it, indicating that the flow ran successfully, without any errors.
Check your SQL DB to confirm that the data has successfully been inserted.
Congratulations, you've just made your first Salesforce Flow with Synatic!
Things to try