SFDC – Public Media – SQL to Salesforce Data Migration – Success Story

Client Overview

The client is a US-based nonprofit Public Media organization who owns, operates produces and distributes public radio and classical music programs in the nation.

Business Challenge

Client needed to migrate their entire organization data, which was stored on SQL database to Salesforce platform. The data migration process had the following challenges:

  • Mapping and conversion of SQL tables with Salesforce objects

  • Scripting for 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

  • Apex

  • JDK 1.6

  • SQL Server Management Studio (SSMS) tool

  • Structured Query Language (SQL)

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. Listed below are some of the steps taken by Eternus Solutions team to achieve seamless Data Migration from SQL to Salesforce:

  1. Eternus Solutions team, along with the client’s database experts, reviewed the existing SQL 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.

  2. Client’s database experts used these results and analyzed the data that would not be needed to migrate. This analysis enabled Eternus Solutions team to determine the actual number of migration job effort needed for the migration of SQL database.

  3. SQL database backup was taken for all legacy data in the form of CSV files.

  4. Eternus Solutions database consultants wrote procedures and functions to hold the transformation views into the SSML tool using SQL scripting language.

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

  6. Relational Junction 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 SQL database tables after migrating to Salesforce.

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

Salesforce-Relational Junction Integration Eternus Solutions project team was responsible for the integration with Relational Junction tool for data replication and integration into Salesforce. The Salesforce-Relational Junction 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-Relational Junction integration was bi-directional.

SupportThe project involved provision of support for the data transformation process in order to create chunks of new data for data migration.

Development Methodology

Business Benefits

  • Simplified daily automated updates

  • Reduced business risk

  • Eliminated data loss

  • Access and ability to report on integrated data

  • 3-Step development methodology that consisted of development of Procedures and functions using SQL scripting in Toad tool, testing in Sandbox or Production org and moving to the Production org

  • Design documents for procedures and functions written in SQL, 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 replication and integration tool Relational Junction

  • Development using SFDC standards and best practices

Share This Case Study