Adapters are recommended when writing data to a Target application, such as ERP or CRM. SQL or Oracle connections are recommended when querying Source from these databases. 

Target

An Adapter uses the target application’s API, which respects the business rules of the application.  By using the API, the Adapter prevents a DTS from creating records or entering data that a user would not be allowed to enter via the application’s User Interface.

Example

Suppose a customer calls and orders 10 more of an item ordered on their last Sales Order. Assume the last Sales Order has been fully shipped and invoiced. A new Sales Order must be created.
Database table and field names for shipped and invoiced Sales Orders are very similar to those for new Sales Orders. Depending on the method used to create the new Sales Order, records could be entered in the wrong table preventing the new Sales Order from being shipped.

• If the user creates the new Sales Order using the application’s User Interface, the application takes care of entering the data in the correct tables.
• If the user creates the new Sales Order using an Adapter and a DTS, the API does not allow the DTS to modify the tables of shipped and invoiced items. The error would be discovered before the record was entered in an incorrect table.
• If the user enters the new sales order using a SQL or ODBC connection and a DTS, the record could be entered accidentally into the shipped and invoiced tables because these connections do not use the business rules that would prevent this error.

Source

A Source connection only reads data from the database, therefore the application’s business rules are not necessary. Configuring a direct connection to the database, such as a SQL or ODBC connection, is recommended because it eliminates the overhead of the Adapter code and more significantly the API code. Retrieving data is faster using a direct connection to the database.
 

How to determine when it is better to use an Adapter vs. a SQL or ODBC Connection in TIBCO Scribe Insight.

TIBCO Scribe Insight