The following figure shows a mapping that sends the ID from the Source Qualifier to an input parameter in the Stored Procedure transformation:
The Stored Procedure transformation passes an output parameter to the target. Every row of data in the Source Qualifier transformation passes data through the Stored Procedure transformation.
Although not required, almost all connected Stored Procedure transformations contain input and output parameters. Required input parameters are specified as the input ports of the Stored Procedure transformation. Output parameters appear as output ports in the transformation. A return value is also an output port, and has the R value selected in the transformation Ports configuration. For a normal connected Stored Procedure to appear in the functions list, it requires at least one input and one output port.
Output parameters and return values from the stored procedure are used as any other output port in a transformation. You can link these ports to another transformation or target.
To configure a connected Stored Procedure transformation:
Create the Stored Procedure transformation in the mapping.
Drag the output ports of the Stored Procedure to other transformations or targets.
Open the Stored Procedure transformation, and select the Properties tab.
Select the appropriate database in the Connection Information if you did not select it when creating the transformation.
Select the Tracing level for the transformation.
If you are testing the mapping, select the Verbose Initialization option to provide the most information in the event that the transformation fails.