When you import a stored procedure, the Designer creates ports based on the stored procedure input and output parameters. You should import the stored procedure whenever possible.
There are three ways to import a stored procedure in the Mapping Designer:
Select the stored procedure icon and add a Stored Procedure transformation.
Click Transformation > Import Stored Procedure.
Click Transformation > Create, and then select Stored Procedure.
When you import a stored procedure containing a period (.) in the stored procedure name, the Designer substitutes an underscore (_) for the period in the Stored Procedure transformation name.
To import a stored procedure:
In the Mapping Designer, click Transformation > Import Stored Procedure.
Select the database that contains the stored procedure from the list of ODBC sources. Enter the user name, owner name, and password to connect to the database and click Connect.
The folder in the dialog box displays FUNCTIONS. The stored procedures in this folder contain input parameters, output parameters, or a return value. If stored procedures exist in the database that do not contain parameters or return values, they appear in a folder called PROCEDURES. This applies primarily to Oracle stored procedures. For a normal connected Stored Procedure to appear in the functions list, it requires at least one input and one output port.
You can select Skip to add a Stored Procedure transformation without importing the stored procedure. In this case, you need to manually add the ports and connect information within the transformation.
Optionally, use the search field to limit the number of procedures that appear.
Select the procedure to import and click OK.
The Stored Procedure transformation appears in the mapping. The Stored Procedure transformation name is the same as the stored procedure you selected. If the stored procedure contains input parameters, output parameters, or a return value, you see the appropriate ports that match each parameter or return value in the Stored Procedure transformation.
In this Stored Procedure transformation, you can see that the stored procedure contains the following value and parameters:
An integer return value, called RETURN_VALUE, with an output port.
A string input parameter, called nNAME, with an input port.
An integer output parameter, called outVar, with an input and output port.
If you change the transformation name, you need to configure the name of the stored procedure in the transformation properties. If you have multiple instances of the same stored procedure in a mapping, you must also configure the name of the stored procedure.
Open the transformation, and click the Properties tab.
Select the database where the stored procedure exists from the Connection Information row. If you changed the name of the Stored Procedure transformation to something other than the name of the stored procedure, enter the Stored Procedure Name.