A USA based company which is the Manufacturer of compressed air products. They are dealing in various products like include air conditioners, jets, coolers, dryers, enclosures, nozzles, pumps and tubes. These products are suitable for cleaning and conveying applications. They provide superior products, unmatched engineering expertise and an industry-best warranty with a 100% performance guarantee.
Client needs were to migrate records form Macola ERP Objects - Quote/Quote Line Items and Opportunity to Salesforce in different Salesforce Objects like Quotes and Opportunity. Our main challenges were :
- Client provide us the Crystal Reports which were used to show the data from Macola ERP. We need to analyse these Crystal Reports and their data sources to identify the actual back-end objects like Tables and Views. Also we need to make the understanding of these Tables with its relationships so that we can figure out on how to pull the data from these data sources.
- We need to fetch the records as shown in the Crystal Reports which does not provide any Connector to connect with Salesforce.
- There were multiple null and duplicate values in different columns of given data which were used as an External Id for different Objects.
- There is no connector available to connect Salesforce and Crystal Reports. Also we did not find any connector for connecting Macola ERP and Salesforce.
- Mapping the different Objects dynamically based on given criteria. They have some criteria for Mapping.
- Multiple Salesforce Objects were involved in Macola Integration which needs to be mapped with each other based on some criteria on Upsert the record.
- They were using Crystal Reports on Macola ERP and we were able to identify the back-end of Macola ERP i.e Microsoft SQL Server.
As Macola uses SQL Server as it’s back-end database, So Astrea has given a solution for this requirement with the help of Talend ETL Tool. Talend has an Open Source Edition for Data Integration which provides the connection for many external systems.
We have used Talend Open Source Data Integration Tool which is easy and free to use to setup the migration Job. Talend provides the connector for both Salesforce and SQL Server. We made the Job as per the requirement in Talend and this Job was responsible for pulling the records from Macola Database and pushing the same on SFDC Objects. This talend Job also have the error handling and reporting capabilities.
- Configured a talend job which fetches the Data from Macola SQL Database and then pushing the same into Salesforce Objects.
- Added filter to process only unique records from Macola Database so that we can avoid the problem of duplicate records.
- Added filter to exclude the records having null values.
- We provide the notifications facility to the admin of the job on the success and failure of job runs.
- Success/Error logs through Emails with the details of Success and Error. Error log contains the details of the exceptions caught during the job failure.
- Details of figures like number of records uploaded/failed into Salesforce Instance.
- We also provide scheduler facility in the job to run the job on recurring basis at every 2 hrs.
So instead of spending time on custom coding as client wants the immediate solution, we used Talend Open Source Data Integration Tool. Maintenance is easy in Talend as, if new changes come in future we can easily change/alter the Talend Job as per new needs.