Pushdown optimization stops at the Lookup transformation <transformation name> because it is downstream from an Aggregator transformation.
The Lookup transformation is downstream from an Aggregator transformation in the pipeline. The Integration Service cannot generate SQL statements for a Lookup transformation downstream from an Aggregator transformation.