Migrating Data from Salesforce to SageCRM

Salesforce.com is a popular cloud CRM, but sometimes on-premise system makes more sense from extra control, cost, or integration point of view. I recently got a chance to convert a client from Salesforce to Sage CRM. The project is still on-going, but there are some highlights that I thought would be interesting for other people who may find themselves in the same position.

Any migration project consists essentially of the same steps:

  1. Get the data out of the source system.
  2. Analyze the data and map it to the target system.
  3. Upload the data into the target system.

1 Getting Data Out of Salesforce

There are two ways of exporting data from Salesforce that I am aware of:

  1. Salesforce built-in Data Export functionality
  2. An external application called DataLoader.io

Each method has its own advantages and disadvantages.

1.1 Built-in Data Export

The built-in data export is accessed in the administrative area of Salesforce, in Data Management -> Data Export. It exports all data into CSV files, can also exports documents and attachments, and combines all of them into a ZIP file that can be easily downloaded. The disadvantage is that you can (theoretically) only do it once a month or once a week, depending on your subscription.

I made the mistake of doing a partial export, and was stuck when Salesforce did not allow me to export remaining information until one-month timeout has passed. Fortunately, their support is excellent, and it only took one phone call to get permission for additional export when I explained my circumstances. Best of all, there was no cost associated with it.

1.2 DataLoader.io

DataLoader.io is a free application that can export everything from Salesforce, including documents and attachments. It worked well on all tables, but kept timing out on attachments, which was a critical component for me. Another inconvenience was that I had to export each table separately; there was no batch export for everything, at least not in the free version. They do have a paid version, but I didn’t investigate it.

At the end, I used the native Data Export in Salesforce.

2 Mapping Salesforce Tables to Sage CRM

The majority of work is in converting the files from Salesforce format into a format that can be used for importing into Sage CRM.

Sage CRM has native import for Companies, Persons, and Leads, but that’s pretty much it. For those entities, you can perform field mapping in Sage CRM UI, as part of the import process. For other entities, information needs to be imported directly into SQL Server.

2.1 Preserving Salesforce Record ID

Every record in Salesforce has a unique ID for its primary key. It is important to preserve its value when importing information into Sage CRM, because it will be necessary later on to establish a link between Sage CRM record IDs and Salesforce record IDs. Therefore, for every entity I imported into Sage CRM, I created a new field called SFID, i.e. comp_SFID, pers_SFID, etc.

2.2 Mapping Companies and Persons

Salesforce export files are in CSV format, but it generates a separate file for companies (Salesforce calls them Accounts) and persons (Salesforce calls them Contacts). Sage CRM, on the other hand, expects just one file that has both companies and persons (unless there are persons not associated with any company). So the two Salesforce export files (Account.csv and Contact.csv) need to be combined together. I prefer creating a new file in Excel, and then copy/pasting columns of data from source CSV files into the target Excel file that I plan to use for importing into Sage CRM.

There is one-to-many relationship between companies and persons. While combining the files, keep in mind that contacts.csv normally would have more records than account.csv, so simply copy/pasting the columns won’t work. I started with the conacts.csv, and then added lookup columns that filled in company information from account.csv for each person. Contact.csv contains reference to the account in the AccountID field, so I simply added as many VLOOKUP formulas as there were columns in the account.csv file. Each VLOOKUP returned data from a different column. Once all VLOOKUPs pulled the data for all rows, I copied them and used Paste Special to paste them as values. This eliminated recalculating the formulas every time I opened the workbook.

Finally, I modified the resulting file by removing unnecessary columns, changed some column names to make then more obvious, and performed standard Data Upload in Sage CRM. Very important point is to make sure Salesforce IDs are mapped to appropriate SFID fields in Sage CRM; this information is required in subsequent uploads.

2.3 Deduplication

CRM performs deduplication automatically as part of the data upload. It is very useful in preventing duplicate companies and persons from being created (that would have to be manually merged later), but also causes an issue with Salesforce import. If several companies in Salesforce have the same company name, only one SFID makes it to the Sage CRM database. This causes problems later on when mapping communications, and opportunities to companies, and there is no good solution to it. One must either turn off deduplication completely, or manually review import files to identify duplicate companies, and adjust their SFIDs, which is very time consuming.

2.4 Mapping Events and Tasks

Events and Tasks are in separate export files in Salesforce, but are both imported into Communication entity in Sage CRM. Sage CRM has two types of communications: Appointment and Task. They store information slightly differently, so one needs to create sample records first to analyze how the data is stored.

There is no native import for Communications in Sage CRM, so it needs to be done directly in SQL Server. But first, we need to convert Salesforce export into a format that matches Communication table design. Here is how I like to do it:

  1. In SQL Server Enterprise Manager, create a new query, make sure CRM database is selected, and then type the following statement:

    select * from Communication
  2. Press Ctrl+E on keyboard to run the query. This will load all record onto screen.
  3. Click anywhere in the query results and press Ctrl+A to select everything.
  4. Right-click and choose Copy with Headers.
  5. Paste into a new Excel file.

This gives me an import template with some sample data. Any field that does not have a value (all rows are NULL) does not need to be in the template, so I delete those. For the rest, I copy paste data from the Salesforce export files.

If the communication is related to a lead or an opportunity, we need to provide Sage CRM lead ID or opportunity ID respectively. Naturally, Salesforce export files won’t have those; they will only have the Salesforce ID for those records. Here is where SFID field come in handy. Assuming leads and opportunities have already been imported, and SFID field populated in them, a lookup can be performed to find matching Sage lead or opportunity ID from a given Salesforce ID.

But that’s not all; company and person associated with the communication record are stored in a separate table, called Comm_Link. Without that information, events and tasks are still accessible using the Find screen, but are not linked to any company or person. Comm_Link import file needs to be prepared separately in order to establish those links.

2.5 Other Entities

Leads import file can be prepared for the built-in Data Upload functionality of Sage CRM. For other entities, SQL import files need to be prepared, similarly to the Communication entity described above.

3 Importing Data into Sage CRM

For companies and persons, the native Data Upload function in Sage CRM works fine. Leads can also be uploaded the same way. For other entities, SQL Server import functionality needs to be used.

Once the import file is populated, right-click on the CRM database and choose Tasks -> Import. SQL Server Import and Export Wizard appears and guides you through the import process.

One other thing to keep in mind is that SQL server import process may truncate text fields, if they are particularly long. This typically happens with the Comm_Note field (called Description in Salesforce), which is basically the body of communication, email, etc. and can be very long. In the database, it is defined as nvarchar(max), but the SQL Server Import Wizard looses data in the import process, because it first uploads information into a temporary table, and it designs the temporary table based on a sample of data, which may not be representative. You need to select advanced settings in the import wizard and specifically tell it to use unlimited text type for this field to avoid import errors.