Importing Tables from SQL Azure

In this tutorial we will show how to import data from the Products, Customers, Orders, and Order Details tables from the Microsoft standard Northwind database on SQL Azure to Salesforce. Here is the schema of the imported data.

northwind-partial

This schema differs from the target Salesforce schema. The data from the Customers table should be imported to Salesforce Accounts and Contacts. Data from the Orders table goes to the Opportunity table. Information from the Products tables should be divided between the Product2 and PricebookEntry Salesforce objects, and information from the Order Details table goes to the OpportunityLineItem Salesforce object.

The problem of such operation is to preserve the relations of the source data when importing them to Salesforce. For example, when we import the Customers table to Accounts and Contacts, the relation should be created between the corresponding Account and Contact in the Salesforce database. This is easy with Skyvia. When inserting data from one table or CSV file, to multiple Salesforce objects, it builds such relations automatically.

ExpandedToggleIcon        Creating Connections

In order to import 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.Specify the connection Name, for example, SQL1.
4.In the Server box, specify enter "TCP:<server name>". Replace "<server name>" with your actual server name.
5.Specify your User Id, Password, and Database to connect.
 
azure-connection
6.Click the Save button to save the connection.

Now we have the necessary connections created. Let's create a package that performs the necessary data import operation.

 

ExpandedToggleIcon        Creating Package

To create a package, perform the following steps:

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 Import.
4.Click CSV near the Source heading and select SQL Server from the drop-down list.
 
source-type-selection
5.Select the SQL1 connection in the Source Connection drop-down list.
 
source-connection-selection
6.In the Target header click Select and select Salesforce in the drop-down list.
7.Select Salesforce1 in the Target Connection drop-down list.
 
target-connection-selection

Now we have an empty package ready for adding tasks. A task is a unit of data extracting, transforming and loading process. When creating an import package, we need to add a task for each source table or CSV file.

ExpandedToggleIcon        Importing Customers Table

The first task we create is a task for importing the Customers table. When creating a package that imports several data files or tables that have complex relations between them, it's better to start with "master" tables or files that does not depend on other tables, then add tasks for tables that depend only on these master tables, then move on to the next dependency level.

To create the task, perform the following steps:

1.Click the + Add task to open the Import Wizard.
2.Select Customers in the Object list and click the Next button in the bottom of the dialog box. You can also switch between the Import Wizard pages by clicking the corresponding icons in the wizard header and return to previous wizard page by clicking the Previous button.
 
source-definition-sql-customers
3.Select Account in the Target list.
 
selecting-account-on-multiple-objects
4.Click the Add Related button. A new drop-down list will appear.
5.In this new drop-down list select Contact.
 
selecting-contact-multiple-objects
6.Click the Next button in the bottom of the dialog box.
7.On this step we need to map the target Salesforce object fields to the source table columns. At first, fields of Account Salesforce object are displayed. As you can see, some columns, such as Phone and Fax were mapped automatically. We use simple column mapping for Account fields - each field is mapped to a source table column. To map a field, simply click it and select the corresponding column from the drop-down list.
 
column-mapping
 
We need to map the following fields:
a.BillingStreet - to the Address column;
b.BillingCity - to the City column;
c.BillingState - to the Region column;
d.BillingPostalCode - to the PostalCode column;
e.BillingCountry - to the Country column;
f.Name - to the CompanyName column.
8.To map Contact object fields, select Account.Contact in the Tables Mapping drop-down list. Note that the AccountId column is mapped to the generated value of the corresponding Account record automatically. Here we only need to map the LastName field to the ContactName column.
 
tables-mapping-contact
9. Click the Finish button to save the task.

ExpandedToggleIcon        Importing Products Table

The next task will import the Products table data to Product2 and PricebookEntry Salesforce objects. Perform the following steps:

1.Click the + Add task to open the Import Wizard.
2.Select Products in the Object list and click the Next button in the bottom of the dialog box.
 
selecting-products-table
3.Select Product2 in the Target list.
 
selecting-product-on-multiple-objects
4.Click the Add Related button. A new drop-down list will appear.
5.In this new drop-down list select PricebookEntry.
 
selecting-pricebookentry-multiple-objects
6.Click the Next button in the bottom of the dialog box.
7.On this step we will map the Product2 fields to the source columns. First, map the Name field to the ProductName column - click Name and select ProductName from the drop-down list.
8.The Products table has the Discontinued column, that determines whether the product is discontinued. Product2 has the IsActive field, which means the opposite. So we need to insert the opposite of the Discontinued column values to the IsActive field. We will use the Expression mapping for this case.
 
Click the IsActive field and then in the Column drop-down list select Expression. Enter "! Discontinued" (without quotes) to the Expression box. The exclamation mark is the boolean NOT operator, which returns the opposite of its argument.
 
expression-mapping-discontinued
9. To map PricebookEntry object fields, select Product2.PricebookEntry in the Tables Mapping drop-down list. Note that the Product2Id column is mapped to the generated value of the corresponding Account record automatically.
10. For PricebookEntry we will map the IsActive column to be always true. Click the IsActive field and then, in the Column drop-down list, select Constant. Select True in the drop-down list below.
 
constant-mapping-bool
11. We also need to map the Pricebook2Id field, which stores the ID of Pricebook, the PricebookEntry belongs to. We will map it to the ID of the standard pricebook, which is automatically created for any Salesforce database. Retrieve this ID and copy it to the clipboard. For example, you may retrieve this ID in the following way:
a.Open a new browser tab or window (do not close the package editor page)
b.Log in to the salesforce.com website.
c.Open the Products tab.
d.In the Maintenance section on the bottom of the Products tab click Manage Price Books
e.Click the link to your standard pricebook. The part of the url of the pricebook page after "https://***.salesforce.com/" will be the pricebook ID. Copy it.
12.  After you copied the ID of the standard pricebook, switch back to the package editor page. Click Pricebook2Id and then, in the Column drop-down list, select Constant. In the Constant box paste the copied ID value.
 
constant-mapping-id
13. Click the Finish button to save the task.

ExpandedToggleIcon        Importing Orders Table

After this we need to import the Orders table. This case is a bit more complex since the Orders table has a foreign key to the Customers table, and we want to preserve this relationship in the Salesforce database as the corresponding reference to the Account object. Skyvia offers two ways to map this relationship: external IDs and source relations. External IDs are used when the referenced data are already imported, and the referenced Salesforce object has an External ID field, which can be used to identify the record to get ID value from. Since by default Account object does not have External ID field, we will use the relation mapping.

1.Click the + Add task to open the Import Wizard.
2.Select Orders in the Object list and click the Next button in the bottom of the dialog box.
3.Select Opportunity in the Target list.
 
selecting-opportunity-object
4.Click the Next button in the bottom of the dialog box.
5.Now we map the AccountId foreign key field using the relation mapping. Click AccountId and then, in the Column drop-down list, select Relation.
6.Select dbo.Customers [FK_Orders_Customers] in the drop-down list below. This drop-down list contains the foreign key relations (note that the name of the item to select may differ if you use schema other than dbo).
 
relaton-mapping-orders
7.Now map the Name and CloseDate fields to the ShipName and OrderDate columns respectively.
8. We will use constant mapping to map the StageName field to a constant. Click the StageName field and then in the Column drop-down list select Constant. Select Closed Won in the drop-down list below.
9. Click the Finish button to save the task.

ExpandedToggleIcon        Importing Order Details Table

When importing the Order Details table, we will also use the relation mapping to preserve the foreign key between Order and Order Details as the relation between Opportunity and OpportunityLineItem. Also we need to store the foreign key between Product and Order Details as the relation between Opportunity and PricebookEntry.

1.Click the + Add task to open the Import Wizard.
2.Select Order Details in the Object list and click the Next button in the bottom of the dialog box.
3.Select OpportunityLineItem in the Target list.
4.Click the Next button in the bottom of the dialog box.
5.Now we map the OpportunityId foreign key field using the relation mapping. Click OpportunityId and then, in the Column drop-down list, select Relation.
6.Select dbo.Orders [FK_Order_Details_Orders] in the drop-down list below (note that the name of the item to select may differ if you use schema other than dbo).
 
relation-mapping-order-details
7.After this we map the PricebookEntryId foreign key field using the relation mapping. Click PriceBookEntryId and then, in the Column drop-down list, select Relation.
8.Select dbo.Products [FK_Order_Details_Products] in the drop-down list below (note that the name of the item to select may differ if you use schema other than dbo).
9.Click the Finish button to save the task.

Now your package is ready. You can optionally click the autogenerated package name and enter a shorter name for the package, for example, Azure Tutorial.

package-editor

Click the Save button to save it. After this you can run the package by clicking the Run button. After you run it, you will see the results of package execution in the right part of the page.