You must perform several steps to use a Stored Procedure transformation in a mapping. Since the stored procedure exists in the database, you must configure not only the mapping and session, but the stored procedure in the database as well.
To use a Stored Procedure transformation, complete the following steps:
Create the stored procedure in the database.
Before using the Designer to create the transformation, you must create the stored procedure in the database. You should also test the stored procedure through the provided database client tools.
Import or create the Stored Procedure transformation.
Use the Designer to import or create the Stored Procedure transformation, providing ports for any necessary input/output and return values.
Determine whether to use the transformation as connected or unconnected.
You must determine how the stored procedure relates to the mapping before configuring the transformation.
If connected, map the appropriate input and output ports.
You use connected Stored Procedure transformations just as you would most other transformations. Drag the appropriate input flow ports to the transformation, and create mappings from output ports to other transformations.
If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
Since stored procedures can run before or after the session, you may need to specify when the unconnected transformation should run. On the other hand, if the stored procedure is called from another transformation, you write the expression in another transformation that calls the stored procedure. The expression can contain variables, and may or may not include a return value.
Configure the session.
The session properties in the Workflow Manager includes options for error handling when running stored procedures and several SQL override options.
ASK INFAPreview
