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.
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
Salesforce.com
JDK 1.6
SQL Server Management Studio (SSMS) tool
Structured Query Language (SQL)
Jitterbit 8.7.4.37
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:
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.
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.
Database backup was taken for all legacy data in the form of .CSV and .MDF files.
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.
Configuration workbook was created as a guide for migration scripting with conversion logic, case translation values to map objects
Jitterbit tool was configured to create staging database with replication tables as a representative of Salesforce objects for data transformation.
Translation object was created in Salesforce org to determine the values of the different database tables after migrating to Salesforce.
Jitterbit tool was used to push all the data of staging database to Salesforce in sequential order to complete the data migration process
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.
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
