The SQL transform allows you to submit SQL commands to generate data to be moved into target objects.
Use this transform to perform standard SQL operations when other built–in transforms do not perform them or when it seems easier to write a single SQL statement instead of using several transforms.
Inputs/Outputs
There is no input data set for the SQL transform. You just specify the source datastore in which the SQL statement must be executed.
There are two ways of defining the output schema for an SQL transform if the SQL submitted is expected to return a result set:
Output Schema Creation
- Automatic:
After you type the SQL statement, select Update schema to execute a select statement against the database that obtains column information returned by the select statement and populates the output schema.
- Manual:
Output columns are defined in the output portion of the SQL transform if the SQL operation is returning a data set. The number of columns defined in the output of the SQL transform must equal the number of columns returned by the SQL query. The column names and data types of the output columns do not need to match the column names or data types in the SQL query.
