Great tips for optimizing SSIS source queries.
Luckily, T-SQL gives us a brilliant query hint to help avoid this.
OPTION (FAST 10000)
This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size. And the effect can be quite significant.