Nearly all components with database connectivity support custom
SQL statements executed in different phases of the transformation.
There are two basic types of SQL properties, queries and scripts.
For both types:
Any SQL accepted by the connected
database system is allowed. Using SQL92 allows you switching to
different database systems without changing the statements.
SBN expressions are allowed.
Queries
SQL queries are used for all components that extract data, mainly the Data Provider and Staging components. The columns of the query result set define the respective output port structure.
Scripts
A SQL script consists of one or more SQL statements that do not return any data. For example, there are properties allowing you to execute SQL statements during initialization (preprocessing) of a component or after completion (postprocessing) of the project.
Using the SQL Property Window
To modify the value for a SQL property of a component, click the respective Edit icon in the Properties window.
Entering SQL Statements
You can enter the SQL statements in the Query text field of the SQL Property window.
Executing SQL Statements and Viewing Query Result Sets
To execute the current SQL statements, click the Execute icon. For Query properties, you are prompted to specify the number of records to view. The result is shown in the Data Viewer.