SFDC – Public Media – Data Migration with Jitterbit- Success Story

Client Overview

The client is a US-based Public Media organization that is renowned for serving the nation with the largest and best quality of educational content.

Business Challenge

Client needed to migrate their entire organization data, which was stored on multiple source systems, including .CSV files, data on the FTP server to Salesforce platform. The data migration process had the following challenges:

  • Mapping and conversion of heterogeneous files with Salesforce objects

  • Scripting for legacy data conversion

  • Design of the data migration flow

  • Deduplication of data

  • Trigger handling for data migration

  • Creation of chunks of the new record for migration

Technical Requirements

  • Salesforce.com

  • JDK 1.6

  • SQL Server Management Studio (SSMS) tool

  • Structured Query Language (SQL)

  • Jitterbit 8.7.4.37

Solution Strategy

Eternus Solutions reviewed the objectives and worked with the client in the capacity of an Offshore Delivery Center, collaboratively taking care of the entire database design and development. Eternus Solutions database expert team designed and implemented their innovative ETL (Extract-Transform-Load) method for successful data migration. This included the following process:

  1. Eternus Solutions team reviewed the existing heterogeneous database source tables and the data stored within. Source tables and target objects combinations, along with total record count for each recorded data were identified. For CSV, the team checked and verified the data formats.

  2. The team used these results and analyzed the data that would not be needed to migrate. This analysis enabled the team to determine the actual number of migration job effort needed for the migration of various databases.

  3. Database backup was taken for all legacy data in the form of .CSV and .MDF files.

  4. Eternus Solutions database consultants wrote stored procedures, scripts and functions to hold the transformation views into the SSML tool using the required scripting language. For CSV data, the team used Microsoft Office tools to create the required .CSV formats.

  5. Configuration workbook was created as a guide for migration scripting with conversion logic, case translation values to map objects

  6. Jitterbit tool was configured to create staging database with replication tables as a representative of Salesforce objects for data transformation.

  7. Translation object was created in Salesforce org to determine the values of the different database tables after migrating to Salesforce.

  8. Jitterbit tool was used to push all the data of staging database to Salesforce in sequential order to complete the data migration process

  9. Based on the unique identifiers, the team updated the Salesforce IDs in the staging databases to maintain the relationships.

Salesforce – Jitterbit Integration Eternus Solutions project team was responsible for the integration with Jitterbit for data replication and integration into Salesforce. The Salesforce-Jitterbit integration enabled the client to migrate the legacy data and build a Salesforce-based reporting warehouse by creating and synchronizing a local database, which is an exact replica of their Salesforce data. The Salesforce-Jitterbit integration was bidirectional.

Development Methodology

  • 3-Step development methodology that consisted of development of Stored Procedures, scripts and functions using Microsoft Management Studio, testing in Sandbox or Production org and moving to the Production org

  • Design documents for stored procedures, scripts and functions, along with source and target mapping documents and migration flow

  • Trigger handling for data migration process

  • Optimized joins for data deduplication

  • Removal of hardcoded values using transformation tables

  • Integration with data integration tool Jitterbit

  • Development using SFDC standards and best practices

Share This Case Study