SSIS: Is the ole source adapter in SSIS parameterized when passing the ? parameters

Question

I assume that by intelligent design the source adapter is parameterized but just wanted confirmation from an SSIS veteran.

As an example if I have a source query in the adapter like:

select
files
,transactions
,dateofsomething

from
reallygoodtablename

where
theparameter = ?

Does this get prepared by sql and get a cached plan or does it get to SQL as an ad hoc query were the ? is already populated.

Answer 1

Unless you are calling the code from a parameterized stored procedure, it's treated like any other ad hoc query. There's a chance you will get a cached plan if you are running the query frequently just as for any other ad hoc query, but it really is no different than if you ran a query, disconnected, connected again, and reran the query.