The SSIS Cache Connection Manager was introduced as a means of performing a lookup against a non OLEDB data source. Once inside the Data Flow, under Data Flow Transformations within the toolbox, you will see a Cache Transform.
Mike Davis walks through a good example of how to create and use a Cache Connection Manager to achieve a lookup from a non OLEDB data source Mike Davis Does Lookup Cache Connection Managers.
The downside of lookups is that package errors involving either connection (cache connection manager) or row level issues can be difficult to track down.
To troubleshoot connectivity issues with the source of the Cache Connection Manager, in SSIS Logging, select the details tab at the package or container level, and select the “Diagnostic” event – reference Package Troubleshooting.
To troubleshoot row level problems be sure to redirect Error output for the transform or OLDEB Destination to a file or database connection manager. For example, on the OLEDB Destination, select Edit, then set the handling of errors to “Redirect Rows”. Then setup the Connection Manager for the redirected rows. This will give you a shortlisted set of rows to inspect after insert failure to the OLEDB Destination.
More detailed coverage of the Lookup Transformation is outlined here Lookup Transformation.