Forcivity Tech Corner

As businesses grow and evolve, their tech stack will evolve with them to address new needs. This may require some trial and error to find the best tech stack for your business. That’s why data migration between systems is such a common occurrence. Here at Forcivity, we ran into this situation with a client who needed help migrating data from Zendesk to Salesforce Service Cloud. The migration project was a success, and we were able to get everything transferred with ease, including the tricky Zendesk comments on tickets.

It can be challenging for businesses to successfully migrate data between platforms without the help of professionals. Many systems lack native migration support. It turns out they aren’t keen on helping their customers leave the platform. We want to share our simple method of how to migrate case comment data from Zendesk to Salesforce Service Cloud to help ease your transition between platforms. Here’s our step-by-step guide to Zendesk case comments to Salesforce Service Cloud migration.

Challenges to Data Migration from Zendesk to Salesforce Service Cloud

When trying to migrate data between Zendesk and Salesforce Service Cloud, there are a few challenges that will impede your progress and success. These are challenges that we anticipated and overcame during our project, but knowing them upfront can save you valuable time and effort. You’ll avoid other solutions that don’t address these issues. Here are the primary obstacles to data migration between the two platforms:

  • There is no native migration capability between the two platforms.
  • When migrating data from Zendesk to Salesforce, the data exports in one large XML file, with no way to filter or parse down to a more micro level.
  • Size constraints make including comments on tickets particularly challenging. With them, the XML output would be several gigabytes large. This is nearly impossible to work with and completely lacking the option to filter down through the comments.
  • There is potential loss or destruction of data (broken links, missing formatting, etc.).

Step-by-step Data Migration from Zendesk to Salesforce Service CLoud

Luckily, this is a fairly straightforward process. With it you can easily migrate Zendesk case comments into Salesforce. As a heads up, this solution requires Microsoft SQL Server. Either the free version or SQL Server Express may be used, and are both viable options. That said, here’s how to successfully migrate Zendesk case comments data to Salesforce Service Cloud in three easy steps.

Step 1: Create two SQL Server database tables

  • Create an SQL server table to house the mapping for Salesforce Case ID and Zendesk Case ID. This will set up the foundation for the relationship between these two items.
  • Create a SQL server table to house the Zendesk case comments you want to migrate.(SEE FIGURE 1)

Figure 1:

Zendesk to SFDC migration
CREATE TABLE [dbo].[CaseComments]( 

        [ZDCaseID] [varchar](50) NULL, 

        [PostType] [varchar](50) NULL, 

        [DateCreated] [datetime] NULL, 

        [IsPublic] [varchar](50) NULL, 

        [Comment] [varchar](max) NULL 

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

Step 2: Begin the export and import of data between Zendesk and Salesforce

  • Export your Zendesk cases and import them into Salesforce. Be sure to add the Zendesk Case ID as a new field in Salesforce during this import. This will be important for matching the comments to the cases later.
  • Next, export these cases from Salesforce into the newly created SQL database table from step 1a. You will need the Salesforce Case Record ID and the Zendesk Case ID.
  • Export the case comments from Zendesk. This export creates a file called ‘tickets.xml’.

Step 3: Execute the SQL code

  • Run the import SQL code (below) to insert the case comments from ‘tickets.xml’ into the newly created table from step 1b. (SEE FIGURE 2)
  • Run a SQL query joining the Case Data and Case Comments data by Zen Desk Case ID, and export this to a CSV file.

Figure 2:

Declare @fileData  XML 

Select @fileData = BulkColumn from OpenRowSet(Bulk'C:\tickets.xml',Single_blob) x; 

INSERT INTO CaseComments (ZDCaseID,PostType,DateCreated,IsPublic,Comment) 

select  

   xData.value('../../nice-id[1]','varchar(20)') ID, 

   xData.value('type[1]','varchar(50)') PostType, 

   xData.value('created-at[1]','DateTime') DateCreated, 

   xData.value('is-public[1]','varchar(50)') IsPublic, 

   xData.value('value[1]','varchar(max)') Comment 

from @fileData.nodes('./tickets/ticket/comments/comment') As x(xData); 

Stay tuned for more!

Migrating Zendesk case comments into Salesforce Service Cloud is the trickiest part of the project, but it’s only one piece. Be sure to follow @Forcivity for more tech talks in the future, including a full walk-through of Zendesk to Salesforce migration.

Jim Coleman

Jim Coleman