Manually Creating an SQL Transformation
You can manually create an SQL transformation. Manually create the transformation when you configure a transformation that runs an SQL query. You might also manually create a transformation that calls a stored procedure when the procedure is not available to import. When you manually create the transformation, you configure the input and output ports and type the SQL statements in the SQL Editor.
1. Select a project or a folder in the Object Explorer view.
2. Click File > New > Transformation.
The New dialog box appears.
3. Select the SQL transformation.
4. Click Next.
5. Select Create as Empty.
6. Enter a name for the transformation and enter the repository location for the transformation.
7. Click Finish
8. Click the Overview view to add ports to the transformation.
9. To add an input port, click Input in the Ports panel to indicate where to add the port. Click the New button and enter the port name, the native type, and the precision.
The default database type is Oracle. The Developer tool shows native types for Oracle databases unless you change the database type on the Advanced view.
10. To add an output port, click Output in the Ports panel before you add the port. Click the New button and enter the port name, the native type, and the precision.
The SQLError port is the first output port by default.
11. In the Advanced view, select the database type that the SQL transformation connects to. Configure other advanced properties for error handling and other optional properties.
When you choose the database type, the Developer tool changes the native datatypes of the ports on the Overview view.
12. Type the SQL query or stored procedure call on the SQL view. Select ports for parameter binding or string substitution in the SQL Editor.
If the stored procedure returns a result set, you must enter the stored procedure call with a syntax similar to the following syntax: (?Field1?,?Field2?,?Field3?) = call Stored_Procedure_Name (?Input_Parm?).
Define the SQL Query