You can configure the SQL transformation to process a saved query or a user-entered query that runs against a Microsoft SQL Server or Oracle database. When you configure the SQL transformation to process a query, you create an active transformation. The transformation can return multiple rows for each input row.
When you enter a query, you can format the SQL and validate the syntax. Alternatively, you can create a string parameter to define the query in the mapping task.
You can create the following types of SQL queries:
Static SQL query
The query statement does not change, but you can use query parameters to change the data.
prepares the SQL query once and runs the query for all input rows.
Dynamic SQL query
You can change the query statements and the data.
prepares an SQL query for each input row.
You can optimize performance by creating static queries.