IT experts had used ETL (Extract, Transform, Load) tools for decades in Business Intelligence and Data Integration Projects. When using ETL in BI projects, data is extracted from distributed systems and loaded into a staging database after being transformed to fit some need or a specific format. From this staging area, BI developers can populate data warehouses and perform machine learning and other analytical processes. The IT community had also relied on ETL tools for data migration projects that involve migrating data and consolidating information among all kind of systems.
If most of your data reside in SQL or ORACLE, you may want to use tools like SQL Server Integration Services (SSIS) or Oracle Warehouse Builder (OWB) as a strat. For example, SSIS is a free and excellent ETL tool, but it does not support connecting to known ERP and CRM systems. Many third-party companies like https://www.devart.com/ provide data connectors for SSIS if you have good experience in SSIS and you do not want to try something new.
Sometimes SQL server shops go with third-party ETL vendors as a result of lacking SSIS knowldge or having specific needs that cannot be satisfied by SSIS even coupled with third-party connectors.
Some of the most known ETL tools come from Informatica, SAS, and SAP.
As business operations increasingly required systems to integrate, ETL tools become an obvious solution to integration needs with its already existing connectivity to many data points. But at the same time, this approach became very complicated the more interconnected systems are added to the mix. With the tight coupling and inter-dependencies, a small change might lead to unpredictable impact. Lack of visibility of databases as applications transitioned into the cloud made integration based on ETL tools impossible. It also presented the needs for real-time integration VS. Batch-Based integration.
Enterprise Service Bus (ESB) replaces ETL for data integration, but it does not eliminate them. ETL tools remain focused on BI and Data warehousing. ESB provides API-Based connectivity with real-time integration and isolates applications and databases from one another. The abstraction layer reduces dependencies and provides flexibility. Developers can use prebuilt connectors without the need to understand the underlying database structure. For more information on when to use ESB review the article to ESB or not ESB published by MuleSoft, a leader in the ESB domain. https://blogs.mulesoft.com/dev/mule-dev/to-esb-or-not-to-esb/