Creating an SQL Transformation from a Stored Procedure

1. Select a project or 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 from an existing stored procedure.
6. Browse for and select a database connection.
7. Browse for and select the stored procedure to import.
8. Enter a name and location for the transformation.
9. Click Finish.
The Developer tool creates the ports and the stored procedure call.
10. If the stored procedure returns a result set, you must manually add the output ports and then reconfigure the stored procedure call.
1. a. In the Overview view, click Output in the Ports panel. Click the New button and enter the output port name, the native type, and the precision.
2. b. In the SQL view, change the stored procedure call to use the following syntax: (?Field1?,?Field2?,?Field3?) = call Stored_Procedure_Name (?Input_Parm?)
You can select the input and the output parameters from the Parameter Binding list of ports in the SQL Editor.