In an unconnected mapping, the Stored Procedure transformation does not connect to the pipeline.
The following figure shows a mapping with an Expression transformation that references the Stored Procedure transformation:
However, just like a connected mapping, we can apply the stored procedure to the flow of data through the mapping. In fact, we have greater flexibility since we use an expression to call the stored procedure, which means we can select the data that we pass to the stored procedure as an input parameter.
When using an unconnected Stored Procedure transformation in an expression, we need a method of returning the value of output parameters to a port. Use one of the following methods to capture the output values:
- Assign the output value to a local variable.
- Assign the output value to the system variable PROC_RESULT.
By using PROC_RESULT, we assign the value of the return parameter directly to an output port, which can apply directly to a target. We can also combine the two options by assigning one output parameter as PROC_RESULT, and the other parameter as a variable.
Use PROC_RESULT only within an expression. If we do not use PROC_RESULT or a variable, the port containing the expression captures a NULL. We cannot use PROC_RESULT in a connected Lookup transformation or within the Call Text for a Stored Procedure transformation.
If we require nested stored procedures, where the output parameter of one stored procedure passes to another stored procedure, use PROC_RESULT to pass the value.
The Integration Service calls the unconnected Stored Procedure transformation from the Expression transformation. Notice that the Stored Procedure transformation has two input ports and one output port. All three ports are string datatypes.
To call a stored procedure from within an expression:
1. Create the Stored Procedure transformation in the mapping.
2 In any transformation that supports output and variable ports, create a new output port in the transformation that calls the stored procedure. Name the output port. The output port that calls the stored procedure must support expressions. Depending on how the expression is configured, the output port contains the value of the output parameter or the return value.
3. Open the Expression Editor for the port. The value for the new port is set up in the Expression Editor as a call to the stored procedure using the SP keyword in the Transformation Language. The easiest way to set this up properly is to select the Stored Procedures node in the Expression Editor, and click the name of Stored Procedure transformation listed. For a normal connected Stored Procedure to appear in the functions list, it requires at least one input and one output port. The stored procedure appears in the Expression Editor with a pair of empty parentheses. The necessary input and/or output parameters are displayed in the lower left corner of the Expression Editor.
4. Configure the expression to send input parameters and capture output parameters or return value. We must know whether the parameters shown in the Expression Editor are input or output parameters. We insert variables or port names between the parentheses in the order that they appear in the stored procedure. The datatypes of the ports and variables must match those of the parameters passed to the stored procedure.
For example, when we click the stored procedure, something similar to the following appears:
: SP.SEQ_GEN_TEST ()
This particular stored procedure requires an LS_FLAG value as an input parameter and returns integer value (STUDENT_ID) as an output parameter. How the output parameter or return value is captured depends on the number of output parameters and whether the return value needs to be captured.
If the stored procedure returns a single output parameter or a return value (but not both), we should use the reserved variable PROC_RESULT as the output variable. In the previous example, the expression would appear as:
: SP.SEQ_GEN_TEST (‘Y’, PROC_RESULT)
‘Y’ is an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.
If the stored procedure returns multiple output parameters, we must create variables for each output parameter. For example, if we create a port called varOUTPUT2 for the stored procedure expression, and a variable called varOUTPUT1, the expression appears as:
: SP.SEQ_GEN_TEST (‘Y’, PROC_RESULT, varOUTPUT1, PROC_RESULT)
The value of the second output port is applied to the output port for the expression, and the value of the first output port is applied to varOUTPUT1. The output parameters are returned in the order they are declared in the stored procedure. With all these expressions, the datatypes for the ports and variables must match the datatypes for the input/output variables and return value.
5. Click Validate to verify the expression, and then click OK to close the Expression Editor.
Validating the expression ensures that the datatypes for parameters in the stored procedure match those entered in the expression.
6. Click OK. When we save the mapping, the Designer does not validate the stored procedure expression. If the stored procedure expression is not configured properly, the session fails. When testing a mapping using a stored procedure, set the Override Tracing session option to a verbose mode and configure the On Stored Procedure session option to stop running if the stored procedure fails. Configure these session options in the Error Handling settings of the Config Object tab in the session properties.
The stored procedure in the expression entered for a port does not have to affect all values that pass through the port. Using the IIF statement, for example, we can pass only certain values, such as ID numbers that begin with 5, to the stored procedure and skip all other values. We can also set up nested stored procedures, so the return value of one stored procedure becomes an input parameter for a second stored procedure.
Below is an Example to call the Stored Procedure in an expression:
The table SOURCE_TABLE has set of Student information. It needs to be loaded to the target TARGET_TABLE.
The stored procedure SEQ_GEN_TEST will be given a dummy Input parameter LS_FLAG and it has an output parameter STUDENT_ID which gives an ID for each student dynamically.
Before execution of the workflow:
RECORDS in SOURCE_TABLE:
RECORDS in TARGET_TABLE: