How to Synchronize 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 how to create an 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.

Using Constant Mapping

While in the SQL Azure database all the information about the products with 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 go to Columns Definition tab of the Task Editor. In the Column drop-down list, we select Constant mapping. In synchronization packages it has an additional checkbox — 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 checkbox, 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 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 an export package and then create a synchronization package itself.

Creating Connections

In order to synchronize data from SQL Azure to Salesforce, first we need to create connections to Salesforce and SQL Azure. 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 +NEW in the top menu.
2. Click the Connection button in the menu on the left.
3. In the opened Select Connector page, select Salesforce. To quickly find it, you can either use the Type to filter box or filter connectors by categories using the All list (for Salesforce, select the CRM category).
4. The default name of a new connection is Untitled. Just click it to rename the connection, for example, to 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.

   

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 log in 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.

   

8. Click Create Connection.

   

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

1. Click +NEW in the top menu.
2. Click the Connection button in the menu on the left.
3. In the opened Select Connector page, select SQL Server. To quickly find it, you can either use the Type to filter box or filter connectors by categories using the All list (for SQL Server, select the Database category).
4. The default name of a new connection is Untitled. Just click it to rename the connection, for example, to SQL Azure.
5. In the Server box, enter “TCP:<server name>”. Replace “<server name>” with your actual server name.

6. Specify your User Id, Password, and Database to connect to.

   

7. Click Create Connection.

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

Creating Export Package for Getting Pricebook2 IDs

1. Click +NEW in the top menu.
2. In the Integration column, click Export. The export package details page will open.
3. Rename your package by clicking and editing the package name. The default package name is Untitled. Please note, if you omit this step, the package name will remain Untitled in the list of created packages.

4. Under Source, in the Connection drop-down list, select the Salesforce connection.

   

5. By default, data will be exported to a manually downloaded CSV file.

   

6. Click the Add new link to open the Task Editor.
7. From the Object drop-down list, select Pricebook2. All the Pricebook2 fields are selected automatically.

8. Clear the checkbox next to Pricebook2 to deselect all its fields. Then select the Id and Name checkboxes and click the Save button at the bottom of the Task Editor.

   

9. Click the Create button to create the package.
10. Run the package.

11. Now we have a CSV file with Pricebook names and Ids. Click the corresponding link in the Run History. The History Details window will open.

    

12. Click the number of successful records in the window. The result file will be automatically downloaded to your computer.

Creating Synchronization Package

To create a synchronization package, perform the following steps:

1. Click +NEW in the top menu.
2. In the Integration column, click Synchronization. The sync package details page will open.
3. Rename your package by clicking and editing the package name. The default package name is Untitled. Please note, if you omit this step, the package name will remain Untitled in the list of created packages.

4. Under Source, in the Connection drop-down list, select the Salesforce connection.

   

5. Under Target, in the Connection drop-down list, select the SQL Azure connection.

   

6. Click the button.

7. In the drop-down list, under the Source, select Product2.

   

8. Click the +Related button twice.

9. In both new drop-down lists, select PricebookEntries.

   

10. In the Target list, select the Products table and click Next step.

Source to Target Mapping

Next, you need to configure mapping settings. You can read detailed information about mapping here or follow the steps below:

1. On the Source to Target tab, map the Name column to the ProductName field.

2. Click the Discontinued column, then, in the Column drop-down list, select Expression and enter “! IsActive” (without quotes) to the box below.

   

3. In the drop-down list that is at the top of the Task Editor, select the first PricebookEntry object.

4. Click the UnitPrice1 field and, in the Column drop-down list, select UnitPrice.

   

5. Next select the second PricebookEntry object in the top drop-down list of the Task Editor.
6. This time click the UnitPrice2 field and, in the Column drop-down list, select UnitPrice.

Target to Source Mapping

Since the data is synchronized in both directions, you need to configure mapping settings to load data in a reverse direction. For this, perform the following steps:

1. Click the Target to Source tab and map the ProductName field to the Name column.

2. Click the IsActive field, then, in the Column drop-down list, select Expression and enter “! Discontinued” (without quotes) to the box below.

   

3. In the drop-down list that is at the top of the Task Editor, select the first PricebookEntry object.

   

4. 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.

5. Select the Use this value as filter of target records checkbox. This means that when synchronizing data in the reverse direction (from Salesforce to SQL Server) only records with Pricebook2ID equal to this value are queried, other PricebookEntry records are ignored.

6. Map the UnitPrice field to the UnitPrice1 column.

   

7. 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.

   

8. Repeat steps 3-5 for the second PricebookEntry object, using the Id value of Discount pricebook (for example), and repeat step 6 for UnitPrice2 column.
9. Click Save to save the task.

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 were 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. Click Schedule on the left side of the toolbar.
2. Under Run every, select Week.

3. Under Days of week, select checkboxes with all the workdays.

   

4. Click Occur once at and select Recur every.

   

5. Enter “1” (without quotes) into the Recur every box and click the Set time restrictions link.

   

6. Enter 09:00 and 18:00 to the corresponding boxes.

   

7. Click Now to put the schedule into action immediately or select At a specific time in the Starting list and specify the necessary date and time you want the schedule to be enabled from.

   

8. Click Save to schedule package execution.

After this our package will run automatically every hour between 09:00 and 18:00 of every workday.

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