The Data Integration Service can apply filter optimization with an SQL transformation if the filter condition references only pass-through ports and the SQL transformation does not have side effects.
The SQL transformation has side effects under the following circumstances:
The SQL query updates a database. The SQL query contains a statement such as as CREATE, DROP, INSERT, UPDATE, GRANT, or REVOKE.
The transformation returns NULL rows for SELECT statements that return no results. The rows might contain pass-through port values, SQL error information, or the NUMRowsAffected field.
The Data Integration Service can apply the early selection and push-into optimization methods with the SQL transformation.