I have had a problem at a customer where a source query in a data flow task was slow. I ran the query in Management Studio and it was fast. There was a filter in the query to limit the data for one day, realized with two SSIS Variables passed to the query with the usual SSIS method by putting them in the query as question mark (?). When I ran it in Management Studio I have of course set the filter in the query directly, so I replaced the ? with the real dates.
This was the only difference I found out between being slow in SSIS and fast in Management Studio (OK, to be precise, I also changed the query a little bit to have less IO than in the original one, but this did not change anything dramatically in the time the query took on SSIS).
So I added a script task in SSIS and build up the whole query, including the values from the variables, in VB.net in the script task, and gave it back as another variable. I have used the variable with the query inside than as datasource in the data flow task and… it was fast, also in SSIS.
It seems to me that sometimes passing variables with the placeholders ? in SSIS really generate bad execution plans in SQL Server. I changed some other things in the query, but the biggest effort was to not use the placeholders for the variables and build the query in a script task completely and just pass it to the data source.
By the way… the query (together with two other queries which I have changed accordingly) had a runtime of 3 hours for the SSIS package…. my changes brought it down to 1 min 40 seconds !