Synchronizing Product Data

In this tutorial we will show how to create a synchronization package that synchronizes Salesforce Product2 objects with a slightly modified Products table from the Microsoft sample database Northwind on SQL Azure. This tutorial also demonstrates creating Export package.

The difference between the Products table used in this tutorial and the Products table from the Northwind database is that our Products table has two columns that store two different prices for each product: UnitPrice1 and UnitPrice2.

ExpandedToggleIcon        Using Constant Mapping

While in the SQL Azure database all the information about the products including two prices is stored in a single table, in Salesforce product prices are stored in a separate PricebookEntry object. Thus, one row from the SQL Azure Products table corresponds to one Product2 Salesforce object and two PricebookEntry objects. The first price from the UnitPrice1 column goes to the 'Standard' Pricebook, and the second price goes, for example, to the 'Discount' Pricebook. If you don't have such a pricebook, you may use any other pricebook or create a new one for this tutorial and name it 'Discount'.

To implement this scenario we will use Constant mapping. In Synchronization packages it has an additional check box - Use this value as filter of target records. If you set constant mapping for one direction (for example, from source to target) and select this check box, only the data having the column values equal to the specified constants participate in synchronization when performing synchronization in opposite direction.

In our tutorial we map the fields of the Product2 object and two PricebookEntry objects to the columns of the Products table. We will simply map the Pricebook2Id fields of the PricebookEntry objects to the constant values equal to the IDs of the Standard and Discount Pricebook2 objects. The UnitPrice field of these PricebookEntry objects will be mapped to the UnitPrice1 and UnitPrice2 columns respectively.

After this, when synchronizing data from SQL Azure to Salesforce, the PricebookEntry objects will refer to the necessary Pricebooks since their ID are assigned to them. When synchronizing data from Salesforce to SQL Azure and getting values for values for UnitPrice1 and UnitPrice2 columns, only the PricebookEntry objects with the corresponding Pricebook2Id values, specified in the constant mapping, will be retrieved from Salesforce for synchronization, and thus the correct values are applied.

In our tutorial we will get the ID values of Salesforce Pricebook2 with Export package and then create the synchronization package itself.

ExpandedToggleIcon        Creating Connections

In order to synchronize data from SQL Azure to Salesforce, first we need to create connections to Salesforce and SQL Azure databases. If you have already created the necessary connections, you may skip these steps. To create a connection to Salesforce, perform the following steps:

1.Click Connections in the menu on the left side of the page to switch to the Connections page.
2.On the Connections page click the New button.
3.In the opened pane click Salesforce.
4.Specify the connection Name, for example, Salesforce1.
5.From the Environment drop-down list select the Salesforce environment type to import data to. Since this is just a sample walkthrough, the Sandbox environment is recommended.
 
salesforce-connection-oauth
6.From the Authentication drop-down list select the authentication method for connecting to Salesforce. If you don't mind storing your Salesforce credentials on Skyvia server, select User Name & Password. If you prefer not to store your credentials, select OAuth 2.0.
7.If you have selected User Name & Password, on the previous step, specify your Salesforce account e-mail, password, and security token. Otherwise, if you have selected OAuth 2.0 authentication, click the Log In with Salesforce button and login via the Salesforce website on the opened page. The result OAuth token will be stored in the connection data. Your Salesforce credentials will not be stored on our website.
 
Login-to-Salesforce    
8.Click the Save button to save the connection.
 
salesforce-connection-oauth-filled

To create a connection to SQL Azure, perform the following steps:

1.Click Connections in the page header to switch to the Connections page.
2.On the Connections page click the New button.
3.In the opened pane click SQL Server.
4.Specify the connection Name, for example, SQL1.
5.In the Server box, specify enter "TCP:<server name>". Replace "<server name>" with your actual server name.
6.Specify your User Id, Password, and Database to connect.
 
azure-connection
7.Click the Save button to save the connection.

Now we have the necessary connections created. Let's create an export package that exports the necessary Pricebook2 ID values.

ExpandedToggleIcon        Creating Export Package for Getting Pricebook2 IDs

1.Click Integration in the menu on the left side of the page.
2.Click the New button in the top right corner of the page.
3.Click Export.
4.In the Source header click Select and select Salesforce in the drop-down list.
5.Select Salesforce1 in the Source Connection drop-down list.
 
source-connection-salesforce
6.Click the + Add task to open the Task Editor.
7.From the Object drop-down list select Pricebook2.
8.Select the Id and Name check boxes and click the Save button at the bottom of the Task Editor.
 
export-pricebook-task
9.Click the Save button in the package header.
10. Run the package.

Now we have a CSV file with Pricebook names and Ids. You can download it by clicking the corresponding link in the Run History.

ExpandedToggleIcon        Creating Synchronization Package

To create a Synchronization package, perform the following actions:

1.Click Integration on the menu on the left side of the page.
2.Click the New button in the top right corner of the page.
3.Click Synchronization.
4.Optionally click the automatically generated package name (on this step it's "Sync Package") and specify the package name.
5.Click Select in the Source header and select Salesforce in the drop-down list.
6. In the Source Connection drop-down list select the Salesforce1 connection.
 
select-source-salesforce-connection
7. Click Select in the Target header and select SQL Server in the drop-down list.
 
target-type-selection-sync
8. In the Target Connection drop-down list select the SQL1 connection.
 
select-target-connection-sync-sql
9. Click the + Add task button.
10. Click the btn-many-to-one button.
11. In the drop-down list under the Source select Product2.
 
select-product2
12. Click the Add Related button twice.
13. In both new drop-down lists select PricebookEntries.
 
product2-pricebookentries
14. In the Target list select the Products table and click Next.
15. Map the Name column to the ProductName field.
16. Click the Discontinued column, then, in the Column drop-down list, select Expression. Enter "! IsActive" (without quotes) to the box below.
 
sync-mapping-discontinued
17. Click Target to Source. Note that the ProductName field is automatically mapped to the Name column.
18. Click the IsActive field, then, in the Column drop-down list, select Expression. Enter "! Discontinued" (without quotes) to the box below.
 
sync-mapping-isactive
19. In the drop-down list that is at the top of the Synchronization Task Wizard select the first PricebookEntries object
 
first-pricebookentry
20. Click the Pricebook2Id field. For this field we will use the constant mapping, as described above. Open the CSV file with exported pricebook data and copy the Id value of the standard pricebook. Then switch back to the browser with the package editor, click the Pricebook2Id field, select Constant in the Column drop-down list, and paste the Id value (without quotes) to the box below.
21. Select the Use this value as filter of target records check box. This means that when synchronizing data in the reverse direction (from Salesforce to SQL Server) only records with Pricebook2ID equal to this value are querried, other PricebookEntry records are ignored.
 
constant-mapping-id2
22. Map the UnitPrice field to the UnitPrice1 column.
23. For PricebookEntry we will map the IsActive column to be always true. You can use either Constant or Expression mapping in this case and specify the true constant as an expression. Click the IsActive field and then, in the Column list, select Expression. Enter "true" (without quotes) to the Expression box.
 
sync-mapping-isactive-true
24. Repeat the steps 19 - 21 for the second PricebookEntries object, using the Id value of Discount pricebook and UnitPrice2 column.
25. Click Finish.

ExpandedToggleIcon        Scheduling Package Execution

After we have created the synchronization package, we want to keep the data in sync automatically. For this, we will configure the package to run every hour during workdays. For this we will use the Incremental Update feature. When using Incremental Updates, Skyvia will not synchronize all the data each time when the package is executed. Instead it will detect data that was changed since the last package execution, and then synchronizes these changes. This allows reducing Salesforce calls and thus the cost of the replication operation.

Skyvia uses LastModifiedData and CreatedDate fields to track changes in Salesforce, and it creates its own tracking tables and triggers to track changes in the SQL Server database.

Note that since we used Salesforce connection as the source, its changes have a priority, and if a record was changed both in Salesforce and SQL Server database, Salesforce changes are applied.

Perform the following actions to set the schedule:

1.Under Schedule (at bottom left of the package editor) click the Set schedule button.
 
schedule-empty
2.In the Run every drop-down list select Week.
3.Click all the workdays in Days of week.
 
schedule-week-days
4.Click Occurs once at and select Recur every.
 
schedule-recur-every
5.Enter "1" (without quotes) into the Recur every box and click the Set time restrictions link.
 
schedule-every-hour
6.Set Start at to 8:00 and End at to 17:59.
 
schedule-time-restrictions
7.Save the package.

After this our package will run automatically every hour between 8:00 and 17:59 of every workday.

You can also visit Scheduling Packages to get more detailed information on setting a package schedule.