Extract, Transform, Load (ETL) tools integrate data from multiple and often disparate applications, such as assimilating data from payroll, purchase and sales applications to a cost accounting system. The best ETL tools make life easy, saving hours of painstaking manual work.
Salesforce is a powerful CRM that offers a world of possibilities. However, it is only as potent as the data it contains. Rather than re-invent the wheel by typing in data to Salesforce all over again, or even waste precious hours manually moving files from one location to another, ETL tools get the required data into Salesforce seamlessly.
On the face of it, Salesforce Import Wizards can do the job. However, ETL tools are much more powerful and dynamic. ETL apps make it possible to export data, make modifications to it, and then bring it back into Salesforce. They also provide access to all Salesforce data objects, and offer a good workaround for the limited 50,000 records in Salesforce.
There are plenty of ETL tools for Salesforce in the market, with varying degrees of functionality and popularity. All tools are more or less the same when it comes to functionality, but each has its own strengths and weaknesses. Some tools have developed a reputation for being perfectly suited for specific complex issues. Here are a few common ones that have earned popularity for being a cut above the rest.
1. Apex Data Loader
This solid, no-frills app, with a basic bare-bones interface makes import and export of data very easy.
Apex Data Loader comes with an “upsert” option that helps updating existing records. The app requires a Salesforce ID or an external ID while it attempts to find a match for a record. When a match is found, the record is updated, or else, a new record is created. This is a very handy tool to have when there are frequent updates of information from an external system.
Apex Data Loader also has some other factors that work in its favor. It generates a pair of success and error CSV files with each operation, which is of invaluable help in identifying what did not go through, and why. An Automap button speeds up field mapping process considerably. There is a “Mass Delete” option for unlimited records and all tables, which is much more potent than the Salesforce Mass Delete feature. Last but not the least, this app is free for anyone with a Salesforce login.
The biggest drawbacks are that the app supports the import of only CSV files, and the option to transform the data is very limited.
MuleSoft’s Dataloader.io is a cloud based solution that includes more or less all features of a standard desktop app, including “upsert”. This app has a much nicer look and feel compared to Apex data loader, besides offering some additional functionality such as ability to explore lookup fields by both ID and name, and automating the task of field mapping. However, like Apex Data loader, the functionality to transform records is very limited.
3. JitterBit Data Loader
JitterBit Data Loader is a useful free ETL, best suited for quick ad-hoc tasks. The app scores in its easy management and range of functionality. It offers not just insert, update and “upsert” features, but also options to query, delete and bulk load. The wizard-based graphical point and click configuration make managing this app easy.
This app comes in both free and paid versions. The free version is restricted in terms of functionality. For instance, the free version makes it very difficult to manage specific ETL projects or share projects with other users.
4. Progress Data Direct
Progress Data Direct is a highly reliable and robust ETL tool that combines traditional and new interfaces to achieve superior data connectivity with Salesforce. This tool scores in its efficiency and ease of use. The built-in cache resides entirely within the driver, without needing an intermediate database. This improves performance and limits web service calls to the Salesforce API. Administration is easy, as the tool uses only a single connector to Salesforce across all applications. It is possible to configure the driver without application code changes to load strategic and tactical data.
5. Starfish ETL
Starfish ETL prides itself on its power, flexibility and speed. It is capable of connecting to almost all data source that exists, bringing together multiple databases. It uses pre-defined data maps and allows customization of such data maps. The app is capable of performing some handy actions such as converting data types and duplicate checking, and can run scheduled or in real-time.
6. Talend Open Studio
Talend Open Studio offers a powerful and versatile open source ETL tool for Salesforce, based on Eclipse IDE. This suite delivers a unified platform, offering a unified environment that makes data management and application integration easier. This, combined with an easy to use graphical environment that combines data integration, data quality, MDM, application integration and big data, improves productivity manifold. The biggest plus is that unlike many other extract transform load tools, this one is strong in the transformation function. It is well suited for challenges such as data cleansing, master data management, and enterprise service bus deployment.
On the flip side, the tool is big, complex, and confusing. It handles common objects in a peculiar way, for example.
Bodhtree’s Midas, powered by Pentaho, is a flexible and high performing cloud based ETL tool that delivers seamless bi-integration between Salesforce.com and Oracle E-Business Suite, SAP and other ERPs, and also databases such as MySQL. The tool reduces implementation cost, time and effort. The power of this tool, where processing large volumes of data is done with considerable ease in the cloud, makes it ideally suited for big data stacks.
Midas offers a custom connector for Salesforce and 300+ open source connectors out of the box. This tool also scores in transparency, by depicting all steps underway diagrammatically. The processing takes place in double quick time, without sacrificing quality and reducing costs at the same time.
8. Pentaho Community Edition
Pentaho Community Edition is an open source version that contains almost all functionality of Midas. The advantage of the community version is access to tools that are time-tested by the community and the availability of a strong and vibrant community to help in case of troubleshooting.
Apatar offers an advanced ETL toolset that works seamlessly on most third-party applications and databases. The handy interface makes it possible to undertake complex integration tasks without even a single line of programming or design. The suite generates native SQL automatically.
Apatar is powerful enough to process data spread over several data sources and files, yet it remains lightweight, with small memory and CPU utilization footprints. Installation is equally fast, taking just a matter of seconds. The tool records run and error logs, making diagnosis and troubleshooting easy. The best part is that the fully functional-no limitations Apatar Community Edition is a free tool.
MassEffect is another handy ETL tool for Salesforce. It comes with support for importing and exporting advanced file formats including CSV, MDB and UDL, and has many other unique features such as support for international characters.
At the end of the day, there is no clear-cut dominant ETL tool for Salesforce. Different tools work best for different functions, and the best tool depends on the task at hand.
Image Credit: Ian on Flickr